Export Excel to HTML - convert tables to HTML

I received an interesting question today – on how to easily publish an Excel file to a web page. Although there are a ton of ways to approach this problem (ranging from Excel Services in SharePoint to Excel Interop or ClosedXML) let us say we want to restrict to using only Excel and VBA. Printing Excel to HTML is a very useful feature if you want to publish your data/Workbook online.

The concept itself is very simple as HTML files are text files and therefore the problem is only to structure the data correctly in VBA before saving it to a HTML file.

I wanted to explore today 2 options:

Generating a HTML file via VBA
Generating a HTML file via the Publish feature in Excel

Both options are fairly useful ones – with the first one offering more flexibility and the second one being much easier to use.

Generating HTML via VBA

So lets start with the simple example of generating an Excel file from scratch just with VBA.

We have the following Excel table (ranging from A1 to C3):

The Code

Sub Test()
    RangeToHtml Range("A1:C3"), "test.html"
End Sub

Sub RangeToHtml(rng As Range, fileName As String)
    Dim resBeg As String
    resBeg = "<html><head></head><body><table>"
    resEnd = "</table></body></html>"
    For i = 1 To rng.Rows.Count
        '---Rows---
        resBeg = resBeg & "<tr>"
        For j = 1 To rng.Columns.Count
            '---Columns---
            resBeg = resBeg & "<td>"
            resBeg = resBeg & rng.Cells(i, j).Value
            resBeg = resBeg & "</td>"
        Next j
        resBeg = resBeg & "</tr>"
    Next i
    Call SaveStringToFile(resBeg & resEnd, fileName)
End Sub

Sub SaveStringToFile(str As String, fileName As String)
    Open fileName For Output As #1
    Print #1, str
    Close #1
End Sub

Excel to HTML: The result

Lets see the result (actual HTML posted to this page):

Col1	Col2	Col3
1	2	3
4	5	6

Nothing extraordinary – just a very simple table without any formatting.
What the code does is traverse through the Excel Range replacing rows with the

tag and columns (or rather cells) with the
tag inserting the cell’s contents within. A very simple example.

Excel Publish to HTML feaure

The Publish to HTML feature is a neat capability that allows you to export your entire Workbook as a HTML web page through which you can easily navigate. You can easily Publish your Excel Workbook from VBA or directly from Excel.

To publish the Excel file go to Save As and select Publish to configure the publish options:

Alternatively you can use the VBA code below to achieve the same:

 With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "PublishToHtml.htm", ";Sheet1", "$A$1:$C$4", _
        xlHtmlStatic, "PublishToHtml", "")
        .Publish (True)
 End With

Easy as that! The additional advantage is that the Publish to Excel feature will keep some of your formatting settings e.g. bold, italic fonts etc. Some, however, usually will be omitted e.g. borders.

Conclusions

Without resorting to external solutions there are least 2 easy options of generating html files from Excel. Personally I would prefer to have control over my HTML file and use VBA possibly adding my own css styles and formatting.

Related posts: