Convert Excel to HTML Table Tags – VBA Macro Code

Excel to HTML Table

Do you want to copy the Excel Table to a webpage?

Most of the time, It is easy to represent data in table format. Excel spreadsheet is the one that can create table format data in seconds. But, to make it as HTML page, we need too many tags to be added for each cell. It is not tough, not anymore.

Here is the easy solution.

  1. Download the Excel at end of this page.
  2. Copy the content to this Excel.
  3. Click the Convert ‘Excel to HTML Table’ button.
  4. Get the HTML code written to file.
  5. Use it in your webpage.

If you are already a VBA gig, then just use the free code available in this page, that just does the same.

VBA Macro Code – Convert Excel Table to HTML

This VBA code just loops thru all the rows & columns in the Excel table in the first sheet.

First Row Header: Instead of checking for font style, code in the Excel file attached below, converts the first row as header.

Table Width: Each column width in Excel is set to width of each column in HTML Table.

Sub Convert_Excel_Table_To_HTML()
    Dim htmlTable As String, iSh As Worksheet
    Dim iRow As Double, iCol As Double, iVal As String
    Dim iRowCount As Long, iColCount As Integer, iWidth(100) As Double
    iRow = 1
    iCol = 1
    
    'Get the number of rows and columns
    Set iSh = ThisWorkbook.Sheets(1)
    While iSh.Cells(iRow, 1).Value <> ""
        iRow = iRow + 1
    Wend
    While iSh.Cells(1, iCol).Value <> ""
        iWidth(iCol) = iSh.Columns(iCol).Width
        iCol = iCol + 1
    Wend
    iRowCount = iRow - 1
    iColCount = iCol - 1
    htmlTable = "<table border=1px>"
    
    For iRow = 1 To iRowCount
        'TR
        htmlTable = htmlTable & "<tr>"
        
        For iCol = 1 To iColCount
            'TD or TH Tags
            iVal = iSh.Cells(iRow, iCol).Value
            
            'bold text becomes table heading
            If iRow = 1 Then
                htmlTable = htmlTable & "<th width=" & iWidth(iCol) & ">" & iVal & "</th>"
            Else
                htmlTable = htmlTable & "<td>" & iVal & "</td>"
            End If
        Next iCol
        
        htmlTable = htmlTable & "</tr>"
    Next iRow
    
    htmlTable = htmlTable & "</table>"
    htmlTable = InputBox("Converted HTML Code", , htmlTable)
End Sub

If the data also has any data to be bold then consider changing the code accordingly.

Download Excel to HTML Table Converter

This converter app has all the functionalities explained above as same. Except for this one.

Bold Data: If the row is Bold, then it is considered as Table header. If it is not bold, then it is considered as table cell data.

Download Excel to HTML Table Converter App downloaded 423 Time

Just download this Excel app, Enable security options for macro & content when asked for, then copy paste your data to the first sheet & Click on convert button.

This app only converts spreadsheet into a plain HTML table. The formatting within the table are ignored.

Leave a Reply