Custom VBA class HTML5Filler and how to use it

In Using Code VBA tools to create HTML an approach is presented to making HTML (page or body) content using VBA code from scratch. A common alternative is to work from an existing page or template and let your VBA code only fill in the parts that vary. For this, the Code VBA add-in includes class HTML5Filler. This class lets you insert (or substitute) values in identified locations in the template. These locations are identified conventionally using the HTML element attribute id. Its use and requirements are discussed in the below sections:

Getting started
Load from String or from File
Inserting a value as content of an element;
Setting the value of an element's attribute;
Filling a table using an array of data;
Use or save the HTML;
Output: Text or SaveAs"

Getting started

To get started download and install the Code VBA add-in. (ed:must be simplified) In your project, obtain the demo module by making a dummy Sub and from in toolbar select Code VBA » HTML Page Writer » HTML5FillerDemo. This inserts module modHTML5FillerDemo and class HTML5Filler.

I will now explain the relevant parts in the HTML5FillerDemo procedure whose full code can be found in the inserted module modHTML5FillerDemo. In the first line we have Dim h5f As New HTML5Filler which declares and instantiate variable h5f which will function as our tool for filling HTML. The With block is used to show clearly that all further operations use the HTML5Filler instance.


Dim h5f As New HTML5Filler
With h5f
    '...'
End With

Load from HTML5 page or HTML string

We now .Load either a file or HTML string for the class to work on. In the Demo a string is choosen to not have to worry where the file is located, although the latter choice, using a template file as input for the operations to produce the output from is the common use:

from string, used in the demo:


    .Load "<body>" & vbNewLine & _ 
        "<table>" & vbNewLine & _ 
        "<tr><td></td><td></td><td></td></tr>" & vbNewLine & _  'etcetera

or from file, as commonly used:


    .Load strFile

It is important to note that the second only works with HTML5 pages. This is because the implementation relies on Microsoft XML v6.0

Inserting a value as content of an element

The simplest use of HTML5Filler is to fill the content of an element by specifying its id (p1) and the value to give it. Starting from


 <p id="p1"></p>

calling method below


.FillElement ID:="p1", Value:="my text"

returns


<p id="p1">my text</p>

Setting the value of an element's attribute

You can also use the HTML5Filler to set attributes of elements. To do this, both the element's id, the name of the attribute must be given, and of course the value. The example below sets the values for the src and alt attributes of a given image element. Starting from


<img id="i1" src="" alt=""/>

calling method below


.FillAttribute ID:="i1", Att:="src", Value:="https://www.codevba.com/code-vba-logo.png"
.FillAttribute ID:="i1", Att:="alt", Value:="my logo"

returns


<img id="i1" src="https://www.codevba.com/code-vba-logo.png" alt="my logo"/>

Filling a table using an array of data

In the Demo procedure, arr2D is a two-dimensional array with data you want to have inserted in a certain table. The table in which to insert the array data is identified using an id which is not placed in the <table> element, but in the first row in the table where the data is to be inserted. In the example below we start with three lines, for the sake of argument we assume the first and third lines to be header and footer line respectively. The second line functions as the data insertion line, which is copied for each row in the input array, including all its attributes (for example for row styling, the id attribute is not copied.)


<table>
<tr><td></td><td></td><td></td></tr>
<tr id="t1"><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td></td></tr>
</table>

In the Demo procedure some alternative ways to get the arr2D input filled with data, here's the simplest. It is a 3x3 size array with in the first row, second column 'test' and in the last row, 3rd column '2, 2'.


Dim arr2D(2, 2) As Variant
    arr2D(0, 0) = "A1"
    arr2D(0, 1) = "B1"
    arr2D(2, 2) = "C3"

calling method below


.FillTableFromArray TableRowID:="t1", var2D:=arr2D

returns


<table>
<tr><td></td><td></td><td></td></tr>
<tr><td><A1/td><td>B1</td><td></td></tr>
<tr><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td>C3</td></tr>
<tr><td></td><td></td><td></td></tr>
</table>

Adding ColumnOrder

It is convenient when the array of data and the table have matching column order, but that is not necessarily the case. If they are different, one approach would be to first reorganize the data in a new array. However this is not convenient nor efficient. FillTableFromArray has a third, optional argument ColumnOrder, which avoids having to do the reordering. It is demonstrated in procedure HTML5FillerColumnOrderDemo.


.FillTableFromArray TableRowID:="t1", var2D:=arr2D, ColumnOrder:=",3,2"

Argument ColumnOrder's value is a comma separated list. The list tells the filling algorithm from which array item to fill the current colum (for the current row). In the example, ',3,2' means: keep the first cell empty, fill the second column cell with the value of the third column in the array and fill the third column cell with the value of the second column in the array (of corresponding rows).

Calling the method now returns


<table>
<tr><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td>B1</td></tr>
<tr><td></td><td></td><td></td></tr>
<tr><td></td><td>C3</td><td></td></tr>
<tr><td></td><td></td><td></td></tr>
</table>

Note this the value 'test' this time appears in the third column.

Output: Text of SaveAs">

In the Demo we use the .Text property to display the resulting HTML in a message box or Immediate window, however, you will probably give it some other use such as the HTML body of an Outlook MailItem.

If you loaded from a template file, another likely use is to save the output as a HTML file:


.SaveAs Filename:=strHTMLDocument