Creating HTM file via Excel VBA

Posted on by

This post VBA code never been used in final application by me – but in test files it was used. I’ve spent a little bit time on it. Code is quite interesting becasue of HTML tags used inside to create HTM file that is being opening in internet browser.

Creating HTM file guarantees as the same schema (should be) for all workstations. Plik HTM has been created be me when PDF SAVE AS tool has not been a part of Excel.

Following sources have been used to create HTM file:
1. Create HTM files from sheet data
2. Extension filters for saving files
3. Different table styles in CSS
4. Font size in CSS
5. Polish signs in Firefox

For tests some data have to be added to active sheet – the best as in following image:

How to create HTM file:

' Source:
' Create HTM files from sheet data
' http://www.meadinkent.co.uk/xlhtmltable.htm
' Extension filters for saving files
' http://software-solutions-online.com/2014/03/13/excel-vba-save-file-dialog-getsaveasfilename/
' Different table styles in CSS
' http://stackoverflow.com/questions/7992198/html-how-to-make-2-tables-with-different-css
' Font size in CSS
' http://www.w3schools.com/tags/att_font_size.asp
' Polish signs in Firefox
' http://forums.phpfreaks.com/topic/132413-polish-characters-dont-display-properly/

Sub HTM_Page_Creation()

Dim TempStr
Dim PageName As String
Dim MyFormats As Variant
Dim FirstRow, LastRow, FirstCol, LastCol, MyRow, MyCol As Integer

' ----------------------------------
' HTM FILE LOCALIZATION

PageName = Application.GetSaveAsFilename("HTM_File_Name", "HTM file (*.htm), *.htm", 1, "Save raport")
' Checking if user chosen CANCEL action
If PageName = "False" Then
    MsgBox "CANCELED.", vbOKOnly + vbInformation, "CONFIRMATION..."
    Exit Sub
End If

' ----------------------------------
' VARIABLE DECLARATION

' MyFormats is an array which can contain formats for numbersand dates. Add one element for each table column.
MyFormats = Array("#", "dd mmm yy", "# ##0 pln", "0%")

' HTM table range
FirstRow = 1
LastRow = 6
FirstCol = 1
LastCol = 4

' ----------------------------------
' CHECKING DATA CORRECTION (admit I don't know how it works)

'If error macro is stopped
'If UBound(MyFormats2) < (LastCol - FirstCol) Then
'    MsgBox "No data - code has been stopped", vbOKOnly + vbCritical, "HTM Creation..."
'    Exit Sub
'End If

' ----------------------------------
' HTM FILE CREATION

Open PageName For Output As #1
Print #1, "<html>"
Print #1, "<head>"
Print #1, "<title>COMPANY SALE RAPORT</title>" ' is showing in browser tab
Print #1, "<meta http-equiv='content-type' content='text/html;charset=windows-1250' />" ' polish signs in Forefox
Print #1, "<style type='text/css'>"
Print #1, "body {font-family: Arial, Helvetica; font-size: 11pt; margin-left: 10; margin-right: 10}"
Print #1, "td.td1 {padding: 1pt 3pt 2pt 3pt; border-style: solid; border-width: 1; border-color: #0F5BB9; font-size: 11pt}"
Print #1, "table.table1 {border-collapse: collapse; border-width: 1 ; border-style: solid; border-color: #0F5BB9 }"
Print #1, "p.p1 {font-size: 8pt}"
Print #1, "</style>"
Print #1, "</head>"

Print #1, "<body>"
Print #1, "<h1>PAGE TITLE</h1>"
Print #1, "<p></p>"
Print #1, "<table class='table1'>"
Print #1, "<p></p>"

' Adding data to HTM files
For MyRow = FirstRow To LastRow
    Print #1, "<tr>"
    For MyCol = FirstCol To LastCol
            If MyRow = 1 Then
                TempStr = Cells(MyRow, MyCol).Value
                TempStr = "<b>" & TempStr & "</b>"
                TempStr = "<td class='td1'; align='center'; bgcolor='#58FAF4'>" & TempStr & "</td>"
            Else
                If IsNumeric(Cells(MyRow, MyCol).Value) = True Then
                    TempStr = Format(Cells(MyRow, MyCol).Value, MyFormats(MyCol - FirstCol))
                    TempStr = "<td class='td1'; align='right'>" & TempStr & "</td>"
                Else
                    TempStr = Cells(MyRow, MyCol).Value
                    TempStr = "<td class='td1'; align='left'>" & TempStr & "</td>"
                End If
            End If
        Print #1, TempStr
    Next MyCol
    Print #1, "</tr>"
Next MyRow

Print #1, "</table>"

' HTM file finalization
Print #1, "<p></p>"
Print #1, "<p class='p1'>To search some value use [CTRL]+'F'. Press[Home] to"
Print #1, "back on top. Data might be copied to Excel</p>"
Print #1, "<hr>"

Print #1, "<p><small>Raport date: " & Format(Date, "dd-mm-yyyy") & " | <a href='http://www.meadinkent.co.uk/'>File has been created thanks to  meadinkent</a></small></p>"
Print #1, "<p></p>"
Print #1, "<p><img src='Some image path'></p>"
Print #1, "</body>"
Print #1, "</html>"
Close #1

MsgBox "Done"

End Sub

124 thoughts on “Creating HTM file via Excel VBA”

  1. Pingback: tangerine business account online

  2. Pingback: nằm mơ thấy con chí

  3. Pingback: mo thay cho chet

  4. Pingback: ป๊อกเด้งออนไลน์

  5. Pingback: nằm mơ thấy thỏ

  6. Pingback: mơ thấy xe máy đánh con gì

  7. Pingback: gamescheat

  8. Pingback: cây phong thuỷ phòng làm việc

  9. Pingback: cung song tử hợp với cung nào nhất

  10. Pingback: con gái thiên bình

  11. Pingback: mơ thấy mất xe đạp

  12. Pingback: mơ thấy quay lại với người yêu cũ

  13. Pingback: mơ thấy đứt tay

  14. Pingback: danh bai doi thuong tren pc

  15. Pingback: game dua ngua dat cuoc

  16. Pingback: ดูหนังออนไลน์เต็มเรื่อง

  17. Pingback: Bilåtervinnare Göteborg

  18. Pingback: mơ thấy quả bưởi đánh con gì

  19. Pingback: mơ thấy đồ ăn

  20. Pingback: chiem bao thay di chua

  21. Pingback: mơ ăn chuối

  22. Pingback: mơ bốc mộ

  23. Pingback: nằm mơ thấy tóc rụng

  24. Pingback: mơ chụp ảnh

  25. Pingback: http://instone-international.com/

  26. Pingback: Sicherheitsdienst

  27. Pingback: Tyson vs Jones jr live stream

  28. Pingback: cvv dumps with pin

  29. Pingback: www.squeeqee.co.uk/carpet-cleaning-enfield

  30. Pingback: pasarqq

  31. Pingback: buy real drivers license uk

  32. Pingback: Market Retailers

  33. Pingback: ตู้แปลภาษา

  34. Pingback: ดูการ์ตูนออนไลน์ อนิเมชั่น

  35. Pingback: mơ đánh chết người

  36. Pingback: mơ thấy cá lóc

  37. Pingback: mơ bị sét đánh

  38. Pingback: bị bọ cạp cắn

  39. Pingback: Boka hämtning skrotbil

  40. Pingback: mơ thấy nhà mới đánh con gì

  41. Pingback: mo thay toc

  42. Pingback: read this post here

  43. Pingback: 토토사이트

  44. Pingback: rescator su

  45. Pingback: evdamhft

  46. Pingback: what makes viagra work

  47. Pingback: wat is viagra voor mannen

  48. Pingback: how long does it take azithromycin to enter you system

  49. Pingback: cialis and viagra

  50. Pingback: viagra

  51. Pingback: comprar viagra

  53. Pingback: quick essay writing service

  54. Pingback: write my research paper reddit

  55. Pingback: writing my essay

  56. Pingback: help me in essay writing

  57. Pingback: business essay writing

  58. Pingback: augmentin generic brand

  59. Pingback: where can i get furosemide

  60. Pingback: azithromycin brand name in india

  61. Pingback: ivermectin stromectol

  62. Pingback: ventolin australia

  63. Pingback: acne pills doxycycline

  64. Pingback: prednisolone 5mg price

  65. Pingback: reviews on clomid

  66. Pingback: dapoxetine 30mg reviews

  67. Pingback: diflucan toenail fungus

  68. Pingback: synthroid weight

  69. Pingback: propecia shedding pictures

  70. Pingback: neurontin impotence

  71. Pingback: metformin hydrochloride

  72. Pingback: paxil and oxycodone

  73. Pingback: sanofi aventis plaquenil

  74. Pingback: metformin 500 mg otc

  75. Pingback: propecia

  76. Pingback: where to buy finasteride

  77. Pingback: ordering cialis online australia

  78. Pingback: medication for ed dysfunction

  79. Pingback: medication canadian pharmacy

  80. Pingback: buy cialis cheaper online

  81. Pingback: Zakhar Berkut hd

  82. Pingback: 4569987

  83. Pingback: buy cialis in miami

  84. Pingback: cialis 20mg usa

  85. Pingback: how to buy cialis online uk

  86. Pingback: buy insurance

  87. Pingback: news news news

  88. Pingback: ordering online australia

  89. Pingback: psy

  90. Pingback: psy2022

  91. Pingback: projectio-freid

  92. Pingback: best payday loans turlock

  93. Pingback: Tinder Says I Have A Message But I Can't See It

  94. Pingback: kinoteatrzarya.ru

  95. Pingback: topvideos

  96. Pingback: video

  97. Pingback: viagra with alcohol

  98. Pingback: lisinopril 20mg online

  99. Pingback: afisha-kinoteatrov.ru

  100. Pingback: Ukrainskie-serialy

  101. Pingback: site

  102. Pingback: generic cialis pricing

  103. Pingback: cialis v viagra

  104. Pingback: top

  105. Pingback: cialist the yeloow pill

  106. Pingback: canada cialis online

  107. Pingback: canadian online pharmacy calls

  108. Pingback: cost of cialis without insurance

  109. Pingback: viagra vs cialis vs levitra price

  110. Pingback: best rx online pharmacy

  111. Pingback: buy cialis without perscription

  112. Pingback: cialis online without prescription

  113. Pingback: cialis without presciption in usa

  114. Pingback: declaring prescription drugs us customs

  115. Pingback: buy cialis online viagra

  116. Pingback: where can i buy cialis without a prescription

  117. Pingback: ed medicines

  118. Pingback: valtrex 250 mg 500 mg

  119. Pingback: erectile dysfunction medicines

  120. Pingback: cause of ed

  121. Pingback: viagra without a doctor prescription usa

  122. Pingback: cialis brand name

  123. Pingback: sildenafil viagra 100mg

  124. Pingback: soderzhanki-3-sezon-2021.online

Leave a Reply

Your email address will not be published. Required fields are marked *