Excel to HTML Table Conversion Macro Source Code

Back to: Main Programming Page

This is a very simple Excel Macro that will convert highlighted ranges in Excel into HTML tables. It generates a very simple table, without any formatting. This generates much more streamlined code than the built-in Save As HTML in Excel. It's also simpler than a lot of the other macros I had found online, which copied Excel's formatting to the HTML code, which isn't what you always want to do.

 

Source Code:

Sheet1:
Sheet1 Screenshot


Module1:
Option Explicit

Sub ConvertToHTML()
  Dim RowStart As Integer
  Dim ColStart As Integer
  Dim ColCount As Integer
  Dim RowCount As Integer
  Dim RowEnd As Integer
  Dim ColEnd As Integer
  Dim ctrRow As Integer
  Dim ctrCol As Integer
  Dim IndentString As String
  Dim CellString As String

  'Get Selection Location and Size
  RowStart = Selection.Row
  ColStart = Selection.Column
  ColCount = Selection.Columns.Count
  RowCount = Selection.Rows.Count
  RowEnd = RowStart + RowCount - 1
  ColEnd = ColStart + ColCount - 1
  
  'Get Indent String
  IndentString = Sheet1.TextBoxIndentSpaces.Text
  
  'Start Table
  Sheet1.TextBoxOutput.Text = IndentString + "<table>"
  
  'Make Table Cells
  For ctrRow = RowStart To RowEnd
    Sheet1.TextBoxOutput.Text = Sheet1.TextBoxOutput.Text + vbCrLf + IndentString + "  <tr>"
    For ctrCol = ColStart To ColEnd
      CellString = Sheet1.Cells(ctrRow, ctrCol).Text
      Sheet1.TextBoxOutput.Text = Sheet1.TextBoxOutput.Text + vbCrLf + IndentString + "    <td>" + CellString + "</td>"
    Next ctrCol
    Sheet1.TextBoxOutput.Text = Sheet1.TextBoxOutput.Text + vbCrLf + IndentString + "  </tr>"
  Next ctrRow
  
  'Close Table
  Sheet1.TextBoxOutput.Text = Sheet1.TextBoxOutput.Text + vbCrLf + IndentString + "</table>"

End Sub