VBA – Creating an HTML Document
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.
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.
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 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
'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
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Open Google Search
| th1 td1 | th1 td2 |
|---|---|
| tr1 td1 | tr1 td2 |
| tr2 td1 | |
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
To get the body’s HTML you can do
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.