CODEWITHSUNDEEP
htmlexcelvba
PunchTheNewbie
PunchTheNewbie

Reputation: 181

VBA Excel Content into HTML (create an HTML file from Excel)

Can anyone help me make this code viable?

The idea here is to gather a set row with information on different lines and insert this information all together in an html file.

Some perks on the code that would make me very grateful to you:

1) making it work 8P; 2) some kind of way for the user to choose the place he is going to save the file like a normal save window (if not possible, at least let him choose the name of the file in the assigned folder); and 3) making sure the code captures all non empty lines on the row.

Many thanks to the community for the attention!

What I have come up to is bellow.

Sub CreateHTML()
'Define your variables.
   Dim iRow As Long
   Dim iStage As Integer
   Dim iCounter As Integer
   Dim iPage As Integer

   'Create an .htm file in the assigned directory.
   Dim sFile As String
   sFile = "J:\GEROC1\Avaliação RO\4) CICLOS\ArquivosExportados" & "\test.html"
   Close

   'Open up the temp HTML file and format the header.
   Open sFile For Output As #1
   Print #1, "<html>"
   Print #1, "<head>"
   Print #1, "<style type=""text/css"">"
   Print #1, "table {font-size: 16px;font-family: Optimum, Helvetica, sans-serif;Border -collapse: collapse}"
   Print #1, "tr {border-bottom: 1px solid #A9A9A9;}"
   Print #1, "td {padding: 4px; margin: 3px; padding-left: 20px; width: 75%; text-align: justify;}"
   Print #1, "th { background-color: #A9A9A9; color: #FFF; font-weight: bold; font-size: 28px; text-align: center;}"
   Print #1, "</style>"
   Print #1, "</head>"
   Print #1, "<body>"
   Print #1, "<table class=""table""><thead><tr class=""firstrow""><th colspan=""2"">Ficha de Risco</th></tr></thead><tbody>"

   'Start on the 2nd row to avoid the header.
   iRow = 2

   'Translate the first column of the table into the first level of the hierarchy.
   Do While WorksheetFunction.CountA(Rows(iRow)) > 0
      If Not IsEmpty(Cells(iRow, 23)) Then
         For iCounter = 1 To iStage
            'Print #1, "</ul>"
            iStage = iStage - 1
         Next iCounter
         Print #1, Cells(iRow, 1).Value
         iPage = iPage + 1
         If iStage < 1 Then
            iStage = iStage + 1
         End If
      End If
   Loop

   'Add ending HTML tags
   Print #1, "</body>"
   Print #1, "</html>"
   Close
End Sub

Upvotes: 0

Views: 16404

Answers (1)

PunchTheNewbie
PunchTheNewbie

Reputation: 181

The Code below will create an html file from a table in your excel file. It will write the entire table and search for the number of lines and columns to write it all that is showing.

Please make sure to edit the html section of the table itself. On my example I wanted to transpose lines and columns and print them one by one.

Also, make sure to adjust if you want a table main header with a title. Remember to adjust the colspan.

This example can be tweaked to write any table into an html file.

Lastly, I put in a little extra where it will ask you where you want to save the file. 

Sub CreateHTML()
  'Define your variables.
  Dim iRow As Long
  Dim tRow As Long
  Dim iStage As Integer
  Dim iCounter As Integer
  Dim iPage As Integer
  Dim lastCol As Integer
  Dim lastRow As Integer

  'Find the last Column Number
   With ActiveSheet
       lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   End With

  'Find the last Column Row
   With ActiveSheet
       lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With

  'Create an .html file in the assigned directory.
  Dim sFile As Variant

   sFile = Application.GetSaveAsFilename(fileFilter:="HTML Files (*.html), *.htm")

   If fileName <> False Then
   SaveWorkbook = fileName
   End If

  'Open up the temp HTML file and format the header.
  Open sFile For Output As #1
  Print #1, "<html>"
  Print #1, "<head>"
  Print #1, "<style type=""text/css"">"
  Print #1, "table {font-size: 16px;font-family: Optimum, Helvetica, sans-serif; border-collapse: collapse}"
  Print #1, "tr {border-bottom: thin solid #A9A9A9;}"
  Print #1, "td {padding: 4px; margin: 3px; padding-left: 20px; width: 75%; text-align: justify;}"
  Print #1, "th { background-color: #A9A9A9; color: #FFF; font-weight: bold; font-size: 28px; text-align: center;}"
  Print #1, "td:first-child { font-weight: bold; width: 25%;}"
  Print #1, "</style>"
  Print #1, "</head>"
  Print #1, "<body>"
  Print #1, "<table class=""table""><thead><tr class=""firstrow""><th colspan=""2"">INSERT TABLE MAIN HEADER HERE - WATCH OUT FOR TABLE COLSPAN</th></tr></thead><tbody>"

  'Translate the first column of the table into the first level of the hierarchy.
  tRow = 1

     'Start on the 2nd row to avoid the header. - iRow=2 / tRow is the table header
     For iRow = 2 To lastRow


     For iCounter = 1 To lastCol

  'EDIT HERE TO CHANGE IT TO YOUR LINKING
  Print #1, "<tr><td>"
  Print #1, Cells(tRow, iCounter).Value
  Print #1, "</td><td>"
  Print #1, Cells(iRow, iCounter).Value
  Print #1, "</td></tr>"

     Next iCounter
  Next iRow

  'Add ending HTML tags
  Print #1, "</body>"
  Print #1, "</html>"
  Close
  End Sub

Upvotes: 1

Related Questions