Create HTML files from Excel tables

Imagine a situation where you have data neatly stored in Excel, in a form you’re comfortable with (or stuck with) and you want to combine it to create a beautiful HTML document (or XML, TXT, you name it).
For the sake of argument, let’s assume you have this spreadsheet here:

spreadsheet

and you want to create a list of links with the name of the site in column A, and the url in column B. That, and you want to be able to control every minute aspect of the file you would be creating.

The solution to that problem is the file system object in VBA. You create an object for file operations, like this:

Dim fs as Object
Set fs = CreateObject("Scripting.FileSystemObject")

You then create an object per file you want to use:

Dim f as Object
Set f = fs.opentextfile("myFile.html", 2, True)

What these parameters mean: 2 means the file is open for writing and True that it can be created if it doesn’t exist

You can then add stuff to your heart’s content by using the writeline method.

f.writeline "<ul>"
f.writeline "  <li><a href=""" & cells(1,2) & """>" & cells(1,1) & "</a></li>"
f.writeline "  <li><a href=""" & cells(1,2) & """>" & cells(1,1) & "</a></li>"
f.writeline "</ul>"

And when you’re done, just close your objects.

f.close

Voilà.

result

When I use this technique, I like to have procedures that create the files, create a standard header and footer and write to the file using arguments, rather than using the fileSystemObject methods directly. I do that so I can have really clean, indented files with all the structure under control.
Here is a simple example file:

fso.xlsm

And here’s a more complex project I’ve done using this technique : http://www.sourceoecd.org/rpsv/figures2008/en/index.htm where all 240 html pages  (and 52 images) have been generated from content found in Excel documents. If the content and the output is more complex than the above example, the idea is pretty much identical.

Leave a Reply