Using Code VBA tools to create HTML

The HTML Writer part of the Code VBA add-in is useful both when wanting to output HTML pages and when wanting to make formatted HTML for the body of Outlook MailItems, Tasks, etcetera. This article gives an overview of the tools included.

HTML Elements, fragments and class HTMLElement.cls
Creating HTML tables from different data sources.

There are two routes to find these tools via the Code VBA add-in toolbar

Code VBA » HTML Writer - to create HTML pages, has most HTML elements supported
(Code VBA ») Outlook » HTML Body - only elements useful for email body.

HTML Elements, fragments and class HTMLElement.cls

Prominent in the HTML Writer menu are the tag fragments being supported. Selecting one inserts code fragments based on the (automatically added) class HTMLElement.cls. These fragments make it easy to develop the (nested) html string - see Custom VBA class HTMLElement and how to use it -no more messing with angle brackets and quotes- see sample code below.


Dim img As New HTMLElement
With img
    .Tag = "img"
    .EndWithNewLine = True
    .AddAttribute "src", strImage
    .AddAttribute "alt", strImageAlt
    .AddAttribute "caption", strImageAlt 'for browsers that do not support alt'
End With

Note that you are free to mix the use of HTMLElement with other parts of your HTML as hardcoded strings.

Creating HTML tables from different data sources.

procedures in html writer menu for creating tables

The most common and basic way to present data is in a table. The Code VBA HTML Writer has four procedures for creating tables in your HTML page or string. Thereally fast way to create such a table is from a 2-dimensional array. Thus, the core procedure is CreateHTMLTableFromArray:


Public Function CreateHTMLTableFromArray(var2D As Variant, _ 
                Optional NrOfHeaderRows As Integer = 0) As String
Dim rRow As Range
Dim rCell As Range
Dim strReturn As String
Dim tbl As HTMLElement
Dim tr As HTMLElement
Dim tc As HTMLElement
    Set tbl = New HTMLElement
    With tbl
        .Tag = "table"
        .EndWithNewLine = True
        .AddAttribute "border", "1"
        .AddAttribute "cellspacing", "0"
        .AddAttribute "cellpadding", "7"
        Dim iRow As Long
        Dim iCol As Long
        Dim varContent As Variant
        For iRow = LBound(var2D, 1) To UBound(var2D, 1)
            'Start new html row'
            Set tr = New HTMLElement
            With tr
                .Tag = "tr"
                .EndWithNewLine = True
                For iCol = LBound(var2D, 2) To UBound(var2D, 2)
                    Set tc = New HTMLElement
                    With tc
                        varContent = var2D(iRow, iCol)
                        .AppendContentValue CStr(varContent)
                        If iRow <= NrOfHeaderRows Then
                            .Tag = "th"
                            .AddAttribute "font-weight", "bold"
                            .AddAttribute "align", "center"
                        Else
                            .Tag = "td"
                            If Len(varContent) > 0 And IsNumeric(Expression:=varContent) Then
                                .AddAttribute "align", "right"
                            End If
                        End If
                    End With
                    .AppendContentElement tc
                Next iCol
            End With
            .AppendContentElement tr
        Next iRow
    End With
    strReturn = tbl.Text
    CreateHTMLTableFromArray = strReturn
End Function

Notes:

the code above again uses the HTMLElement class which is automatically added to your project when you select the procedure from the menu;
The optional argument NrOfHeaderRows tells the procedure to apply special heading formatting to the first x rows of the array input. Normally its value will be 0: no header or 1: a single header row at the top;
The data in the non-header rows are formatted conventionally: numerics right-aligned;

The other three procedures only help you get the code running from a common data source

Create HTML Table from Excel Range


Public Function CreateHTMLTableFromRange(ExcelRange As Excel.Range, _ 
                        Optional NrOfHeaderRows As Integer = 0) As String
Dim var2D As Variant
    var2D = ExcelRange
    CreateHTMLTableFromRange = CreateHTMLTableFromArray(var2D, NrOfHeaderRows)
End Function

Create HTML Table from DAO Recordset


Public Function CreateHTMLTableFromDaoRecordset(rst As DAO.Recordset, _ 
                        Optional NrOfHeaderRows As Integer = 0) As String
Dim var2D As Variant
    With rst
        .MoveLast
        .MoveFirst
        var2D = .GetRows(.RecordCount)
    End With
    CreateHTMLTableFromDaoRecordset = CreateHTMLTableFromArray(var2D, NrOfHeaderRows)
End Function

Create HTML Table from ADO Recordset


Public Function CreateHTMLTableFromAdoRecordset(rst As ADODB.Recordset, _
                                    NrOfHeaderRows As Integer = 0) As String
Dim var2D As Variant
    var2D = rst.GetRows()
    CreateHTMLTableFromAdoRecordset = CreateHTMLTableFromArray(var2D, NrOfHeaderRows)
End Function