VBA – Creating an HTML Document

I made a couple recent posts regrading mapping, encoding, decoding, all web related stuff

Access - Google Maps Basics

This is the first in a series of articles on Mapping within Access using the Web Browser control. Today, I thought I’d demonstrate how easy it is to provide a map of an address to your users using Google Maps.

VBA - URL Encode, URI Encode

After presenting a couple recent articles on mapping in which we build up a URL and then needed to replace certain characters to make it compatible with Google/Bing… I thought I’d share a couple functions that can do this more efficiently and perform URL/URI encoding.

VBA - HTML Encode and Decode String

So in my previous post I demonstrated a couple techniques to encode/decode a URL/URI. Today, I thought I’d demonstrate how easy it is to encode a text string into HTML with the proper HTML entities for special characters, and decode an HTML string back to plain text.

so I thought I’d demonstrate how one can use VBA to dynamically create, or manipulate, an HTML document.

Creating a MSHTML HTML Document via VBA

Public Function HF_CreateHTMLDoc() As String
    On Error GoTo Error_Handler
    #Const HF_EarlyBind = False     'True => Early Binding / False => Late Binding
                                    'Typically a Global Module Level Variable
    #If HF_EarlyBind = True Then
        Dim oHTMLFile         As MSHTML.HTMLDocument
        Dim oElem             As MSHTML.HTMLGenericElement
        Dim oLink             As MSHTML.HTMLLinkElement
        Dim oTable            As MSHTML.HTMLTable
        Dim oTr               As MSHTML.HTMLTableRow
        Dim oTd               As MSHTML.HTMLTableCell

        Set oHTMLFile = New MSHTML.HTMLDocument
    #Else
        Dim oHTMLFile         As Object
        Dim oElem             As Object
        Dim oLink             As Object
        Dim oTable            As Object
        Dim oTr               As Object
        Dim oTd               As Object

        Set oHTMLFile = CreateObject("HTMLFile")
    #End If

    oHTMLFile.body.innerHTML = ""    'otherwise the body defaults to <p>&nbsp;</p>

    'Add to the header
    '**************************************************
    'Add meta charset
    Set oElem = oHTMLFile.createElement("meta")
    Call oElem.setAttribute("charset", "UTF-8")
    Call oHTMLFile.head.appendChild(oElem)
    'Add title
    Set oElem = oHTMLFile.createElement("title")
    oElem.innerText = "Testing Title"    'Should Encode!
    Call oHTMLFile.head.appendChild(oElem)
    'Add a script
    Set oElem = oHTMLFile.createElement("script")
    Call oElem.setAttribute("id", "jquery-easing-js")
    Call oElem.setAttribute("type", "text/javascript")
    Call oElem.setAttribute("src", "//cdnjs.cloudflare.com/ajax/libs/jquery-easing/1.4.1/jquery.easing.min.js?ver=6.1.1")
    Call oHTMLFile.head.appendChild(oElem)

    'Add to the body
    '**************************************************
    'Add a paragraph
    Set oElem = oHTMLFile.createElement("p")
    oElem.innerText = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua."    'Should Encode!
    Call oHTMLFile.body.appendChild(oElem)

    'Add a link
    Set oLink = oHTMLFile.createElement("a")
    'Properties are all messed up!
    '    oLink.href = "https://www.google.com" '=> rel
    '    oLink.rel = "https://www.google.com" '=> rev
    '    oLink.rev = "https://www.google.com" '=>urn
    '    oLink.Target = "_blank" 'Appends itself to the href?!
    Call oLink.setAttribute("href", "https://www.google.com")
    Call oLink.setAttribute("target", "_blank")
    oLink.innerText = "Open Google Search"
    Set oElem = oHTMLFile.createElement("p")
    Call oElem.appendChild(oLink)
    Call oHTMLFile.body.appendChild(oElem)

    'Add table
    Set oTable = oHTMLFile.createElement("table")
    Call oTable.setAttribute("id", "myTable")
    Call oTable.setAttribute("border", "1 | 0")
    'Build the header
    oTable.createTHead
    Set oTr = oHTMLFile.createElement("tr")
    'Create cell
    Set oTd = oHTMLFile.createElement("th")
    oTd.innerText = "th1 td1"
    'add cell to row
    Call oTr.appendChild(oTd)
    'create another cell
    Set oTd = oHTMLFile.createElement("th")
    oTd.innerText = "th1 td2"
    'add cell to row
    Call oTr.appendChild(oTd)
    'add row to table
    Call oTable.tHead.appendChild(oTr)
    'Create row 1 *
    Set oTr = oHTMLFile.createElement("tr")
    'Create cell
    Set oTd = oHTMLFile.createElement("td")
    oTd.innerText = "tr1 td1"
    'add cell to row
    Call oTr.appendChild(oTd)
    'create another cell
    Set oTd = oHTMLFile.createElement("td")
    oTd.innerText = "tr1 td2"
    'add cell to row
    Call oTr.appendChild(oTd)
    'add row to table
    Call oTable.appendChild(oTr)
    'Create row 2 *
    Set oTr = oHTMLFile.createElement("tr")
    'Create cell
    Set oTd = oHTMLFile.createElement("td")
    Call oTd.setAttribute("colspan", "2")
    oTd.innerText = "tr2 td1"
    'add cell to row
    Call oTr.appendChild(oTd)
    'add row to table
    Call oTable.appendChild(oTr)
    'add table to body
    Call oHTMLFile.body.appendChild(oTable)

Error_Handler_Exit:
    On Error Resume Next
    Set oTd = Nothing
    Set oTr = Nothing
    Set oTable = Nothing
    Set oElem = Nothing
    Set oHTMLFile = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: HF_CreateHTMLDoc" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Now this is just a sample, but ultimately it creates a proper HTML document like

<html>
    <head>
        <meta charset="UTF-8" />
        <title>Testing Title</title>
        <script id="jquery-easing-js" src="//cdnjs.cloudflare.com/ajax/libs/jquery-easing/1.4.1/jquery.easing.min.js?ver=6.1.1" type="text/javascript"></script>
    </head>
    <body>
        <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>
        <p><a href="https://www.google.com" target="_blank">Open Google Search</a></p>
        <table id="myTable" border="1">
            <thead>
                <tr>
                    <th>th1 td1</th>
                    <th>th1 td2</th>
                </tr>
            </thead>
            <tr>
                <td>tr1 td1</td>
                <td>tr1 td2</td>
            </tr>
            <tr>
                <td colspan="2">tr2 td1</td>
            </tr>
        </table>
    </body>
</html>

Removing Elements

In my example I demonstrate the creation of various elements using createElement and appendChild, but what about deleting/removing elements? Well is it equally simple.

If you have the object variable for the element in question you can simply use removeChild. For instance, to remove the table from the example above we could do:

call oHTMLFile.body.removeChild(oTable)

If on the other hand we don’t have the variable, then we can do something along the lines of:

Dim oTableToDelete as Object
set oTableToDelete = oHTMLFile.getElementById("MyTable")
If Not oTableToDelete Is Nothing Then call oHTMLFile.body.removeChild(oTableToDelete )

So we first find it, in this instance by it’s id using the getElementById() and then we delete it.

Checking the generated HTML

To get the full document HTML you can do

oHTMLFile.documentElement.outerHTML

To get the head’s HTML you can do

oHTMLFile.head.innerHTML

To get the body’s HTML you can do

oHTMLFile.body.innerHTML

Displaying the MSHTML.HTMLDocument in a Web Browser Control

If you want you can push the MSHTML.HTMLDocument and display it in a Web Browser control by doing something like:

If your function returns a MSHTML.HTMLDocument

    With Me.WebBrowser0.Object.Document
        .Open
        .Write HF_CreateHTMLDoc.documentElement.outerHTML
        .Close
    End With

or, if your function return the MSHTML.HTMLDocument as a string

    With Me.WebBrowser0.Object.Document
        .Open
        .Write HF_CreateMSHTMLDocument
        .Close
    End With

So this would allow you to create and display HTML without ever reading/writing anything to the HD.

Word of Caution

If you review my code for creating a link, you’ll notice several lines commented out of things I tried and did not work as expected amongst other oLink.href! So be forewarned that there appear to be bugs with some of the properties/methods. So if you encounter issue, try to find workarounds as I did.

Also note, I already reported some of these issue to Microsoft, for whatever that is worth.

Documentation

Now this was just a generic example to illustrate how you can create a few different elements in the header or body to show how it is done as support documentation is very scarce and even when you do find some it is extremely basic to the point of being pretty much useless. I highly recommend using Early Binding for development and only switching to Late Binding when deploying. The Early Binding intellisense is a life saver in this instance!

Furthermore, Help from within the VBE is useless as whenever I tried using F1 to get some help on any of the properties/methods it always redirected me to a ‘Keyword not found‘ page. Not once did I actually get to a help page relating to MSHTML or the property/method that was actively selected! Once again, this has been reported to Microsoft.

Other Resources on the Subject

HtmlDocument Class (System.Windows.Forms)

Provides top-level programmatic access to an HTML document hosted by the WebBrowser control.

2 responses on “VBA – Creating an HTML Document”

Emanuele November 4, 2023 at 1:01 pm

With Option Explicit, I got an error: It’s necessary “Dim oLink As Object” in the declarations section above.
1. Daniel Pineault Post authorNovember 4, 2023 at 6:34 pm
  
  Sorry about that, I don’t know how I overlooked that declaration in the Late Binding version of the code. Thank you for pointing it out, I have updated the code.