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):
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):
Nothing extraordinary – just a very simple table without any formatting.
What the code does is traverse through the Excel Range replacing rows with the
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.
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.