Excel/VBA : Publishing HTML Reports from Excel

In my opinion, creating reports directly from Excel workbook is a bit old-fashioned way to handle the case. For any official reporting (meant to be published for a wide audience of stakeholders, needs to have traceable history), my recommendation is pretty much the following : process calculations (say, by using third-party software), process results into SQL server database (to store report history), create views using relevant database tables, finally use SSRS by linking report graphical objects with database views for report publishing.

However, there might still be some cases, in which this kind of processing would definitely be considered as overkill. Say, a batch of processed daily calculation results must be shared within the unit for some internal (informal) checking. For such cases, creating report directly from Excel workbook by using publishObject in VBA is quite handy and inexpensive solution. So, what to do then ? First, create a report page (one centralized worksheet) from which all the reports (there can be more than just one report to be published) will be published. For each report, define a named range which covers the exact area to be published. As an example, the screenshot below shows imaginary Excel report what I have created. Note, that the actual file name for HTML file has been defined as a comment property of Excel.Name object. Note also, that the figures in table below are arbitrary.

Next, I have defined a network folder (hard-coded in the program) into which this report will be published as html page. Finally, I have implemented the program (presented below) in Excel, from which the report (or several reports) will be published. As a user will press command button (create reports), the program will create html file, as shown in the screenshot below.

VBA program for handling command button "click" event is presented below.

Option Explicit
'
Private Const folderPathNameForHTMLFiles As String = "C:\temp\HTMLReporting\"
'
Private Sub btnCreateReports_Click()
    '
    ' deactivate command button pressing
    ActiveSheet.Range("A1").Select
    '
    Dim reportRangeName As Excel.Name
    Dim reportRange As Excel.Range
    Dim report As PublishObject
    Dim reports As PublishObjects: Set reports = ActiveWorkbook.PublishObjects
    '
    ' loop through all names within active workbook
    For Each reportRangeName In ActiveWorkbook.Names
        '
        ' preparation for the case, in which the name is corrupted
        On Error GoTo exitPoint
        ' handle only the names related to ranges in reporting worksheet
        If (reportRangeName.RefersToRange.Worksheet.Name = ActiveSheet.Name) Then
            '
            ' disable screen updating
            Excel.Application.ScreenUpdating = False
            ' create Excel range for report
            Set reportRange = Worksheets(ActiveSheet.Name).Range(reportRangeName.Name)
            reportRange.Select
            '
            ' create path and name for HTML report file
            ' file name (including html extension) is defined as comment within Excel name
            Dim HTMLFilePathName As String
            HTMLFilePathName = folderPathNameForHTMLFiles + reportRangeName.Comment
            '
            ' create and publish report as publishedObject object
            Set report = reports.Add( _
                SourceType:=xlSourceRange, _
                fileName:=HTMLFilePathName, _
                Sheet:=reportRange.Worksheet.Name, _
                Source:=reportRange.Address, _
                HtmlType:=xlHtmlStatic)
            report.Publish True
        End If
        '
exitPoint:
    Next
    '
    ' finally, delete all reports from active workbook
    reports.Delete
    Range("A1").Select
    Excel.Application.ScreenUpdating = True
End Sub
'

Finally, thanks a lot for reading this blog.
-Mike