Excel VBA: No Experience Required - HTML Export

In this month’s article, we will use VBA to create a subroutine to export data from an Excel worksheet into an HTML file, and automatically open it in a browser window. We will cover topics such as writing to a text file, the “New” keyword, reading values from controls on a UserForm, passing values as parameters, and briefly, using Windows API functions. While I realize that Excel already provides the capability to save spreadsheets as HTML, this project is intended to be a useful learning exercise, and also gives you the potential to customize the HTML export feature described.

Building the UserForm

To start, we will create a UserForm which looks like this:

In Excel, create a new workbook. Open the Visual Basic Editor (Tools, Macro, Visual Basic Editor) In the VB Editor, click on menu item Insert, UserForm. If the Properties window is not already visible, click on View, Properties Window. Change the (Name) property of your UserForm to HTMLExportForm. Change the Caption property to "HTML Export". Next, using the Toolbox (View, Toolbox), add the following controls to your form and set the Name and Caption properties as listed. (When nothing is listed for a property, leave the default property value).

Also, set the “Default” property of the OK button to True, and the “Cancel” property of the Cancel button to True.

Displaying the UserForm

To actually see our form in action, we will need to create a subroutine to call the form’s Show method. In the VB Editor, click on Insert, Module. An empty code window will appear on the right. As recommended in earlier articles, add the line “Option Explicit” at the top of this new module. Next type “Sub HTMLExport()” and Enter. The “End Sub” appears automatically. This HTMLExport subroutine, as you may have guessed (because you’re clever), will be the subroutine which we will eventually call to export our data to HTML.

In a previous article, we displayed our UserForm using a line of code that looked like this:

HTMLExportForm.Show 1

However, for this exercise, we will do this a bit differently, using the New keyword. New is used to create an instance of an object, which you may then assign to an object variable. Taking a UserForm as an example of an object, you could create the form as we’ve done above, by simply calling its Show method. The problem is that after the form has been closed, it’s gone, and we have no way to extract any data from it (for example, to read the title the user typed in).

In order to be able to read data from the form after it has closed, we need to create a UserForm variable, or more specifically, a variable of type HTMLExportForm (using Dim), create the actual form object itself (using New), and assign the form object to the variable. There are two ways to do this. First,

Dim myform As HTMLExportForm
Set myform = New HTMLExportForm

Or the shortcut version,

Dim myform As New HTMLExportForm

Now that we’ve created the UserForm object, and assigned it to the “myform” variable, we can show it using:

myform.Show 1

After the form closes, we can still access it by name. For example, if we want to display the title entered by the user...

MsgBox myform.txtTitle.Text

If you’re following along in Excel, now would be a good time to try this out. You should have:

Sub HTMLExport()
  Dim myform As New HTMLExportForm
  myform.Show 1
  MsgBox myform.txtTitle.Text
End Sub

Run the code directly from the VB Editor by clicking the blue triangle “play” button on the toolbar. Or, from Excel, type Alt+F8 to bring up a list of available macros, then double-click on HTMLExport. After the form appears, type in a title, then close the form (you’ll have to use the X button in the upper right. The title you typed is displayed in a message box.

To reiterate my earlier point, if you tried the following code instead:

HTMLExportForm.Show 1
MsgBox HTMLExportForm.txtTitle.Text

...the message box would be empty. Since you haven’t “named” your HTMLExportForm object, these two lines of code are actually referencing two separate HTMLExportForm objects.

OK, No Wait… Cancel

Of course, our UserForm doesn’t do anything yet, and not even the OK and Cancel buttons do anything. Our next step, then, is to write some code for the form. In the VB Editor’s Project window (the tree structure in the upper-left), right-click on HTMLExportForm (under the Forms folder), and select View Code. Add the following two lines at the top of the empty code window:

Option Explicit
Public Result As Boolean

Result is declared as a public variable, meaning that it can be accessed from outside of the UserForm’s code. We will use this variable to indicate whether the user clicked OK or Cancel. As you will recall, you can create functions (event handlers) for the OK and Cancel buttons simply by viewing the form in the VB Editor, and double-clicking on each button. Add the following code for these buttons:

Private Sub btCancel_Click()
  Result = False
  Unload Me
End Sub

Private Sub btOK_Click()
  Result = True
  Unload Me
End Sub

Going back to our HTMLExport subroutine, we can now check the value of Result, using myform.Result:

Sub HTMLExport()
  Dim myform As New HTMLExportForm
  myform.Show 1
  If myform.Result Then
    MsgBox myform.txtTitle.Text
  End If
End Sub

Now the message box will only appear if the user clicks OK.

Initializing the Form

So that the user doesn’t have to type everything in, we’ll want to supply default values for the controls on our UserForm. We can do this using the UserForm_Initialize() method. In the code window for HTMLExportForm, select UserForm from the left dropdown at the top of the code window. This will create a subroutine called UserForm_Click. We don’t really want to do anything when the user clicks on the form, so you can delete this subroutine. Instead, select Initialize from the dropdown list of events on the right. This will create our UserForm_Initialize subroutine.

For the default title, we’ll use the name of the currently-active worksheet.

txtTitle.Text = ActiveSheet.Name

Note that this is not necessarily a worksheet within the workbook we are creating, which contains the code. This allows us to export data to HTML from any spreadsheet, not just the one which has the HTMLExport code.

We can also default the checkbox to being checked, using:

xbView.Value = True

(This can also be done without code, directly in the VB Editor’s Properties window)

Next comes the tricky part. We want to set the default file name to be the same as the file name of the current workbook (including full path), but with an extension of “.htm” instead of “.xls”. To do this, we’re going to use the InStr VB function to get the location of the dot (“.”) within the file name of the workbook, then use the Left$ function to retrieve just that portion of the name up to the dot. We can combine this with the new “htm” extension by simply using +. (For the sake of brevity, we’ll go on the assumption that there is no more than a single dot within your path/file name.)

Private Sub UserForm_Initialize()
  Dim shtmlfile As String
  Dim idotpos As Integer
  idotpos = InStr(ActiveWorkbook.FullName, ".")
  If idotpos = 0 Then ' no dot, just append ".htm"
    shtmlfile = ActiveWorkbook.FullName + ".htm"
  Else
    shtmlfile = Left$(ActiveWorkbook.FullName, idotpos) + "htm"
  End If
  txtFileName.Text = shtmlfile
  txtTitle.Text = ActiveSheet.Name
  xbUseHeaders.Value = True
  xbView.Value = True
End Sub

If you haven’t already done so, you should save your workbook before running this code, so that you can see a full path in the default file name.

Writing to a Text File

Visual Basic provides a set of functions for reading from or writing to non-Excel files, in either text or binary format. The general steps for dealing with file input and output are:

1. Select a number which will be used to identify a particular file
2. Open the file, specifying a “file mode” (Input, Output, Binary, etc.)
3. Read data from the file, or write data to the file
4. Close the file

The file number is very important, as it is used to identify the file in all file input/output functions. You can assign your own number, or VB provides a function called FreeFile which guarantees you a file number which is not already in use.

To actually open the file, you will use the mysteriously-named function “Open”. I won’t go into much detail on the various ways you can open a file. If you’re interested, have a look at the Microsoft Visual Basic Help file.

Functions (or Statements) for reading data from a file include Input # and Line Input #. (The file number goes after the # sign.) Input # reads delimited data, while Line Input # reads an entire line. Writing data to a file is done with Write # or Print #. Write # corresponds to Input # in that it is generally used to write comma-delimited data, whereas Print # corresponds to Line Input # and writes a complete line.

Creating the HTML File

Now that we’ve gathered all of the information we need via our UserForm, and we know how to write data to a file, it’s time to create the code to write our HTML. Let’s do this in a new subroutine, separate from our HTMLExport subroutine. We’ll pass the file name and title as string parameters (See Excel VBA Lesson 3 for an explanation of parameters)

Start with the new subroutine:

Private Sub GenerateHTML(ByVal sFileName As String, ByVal sTitle As String)
End Sub

Then call it from the HTMLExport subroutine:

Sub HTMLExport()
  Dim myform As New HTMLExportForm
  myform.Show 1
  If myform.Result Then
      GenerateHTML myform.txtFileName.Text, myform.txtTitle.Text
  End If
End Sub

Before we start trying to generate actual HTML, let’s just try to write a single line of text, namely the title entered in the form, to the specified file:

Private Sub GenerateHTML(ByVal sFileName As String, ByVal sTitle As String)
  Dim iFileNum As Integer
  iFileNum = FreeFile
  Open sFileName For Output As iFileNum
  Print #iFileNum, sTitle
  Close iFileNum
End Sub

The form of the Open function we are using is:

Open pathname For mode As filenumber

Pathname is the name of the file to open, including full path. Mode is a keyword specifying the file mode, which must be one of Append, Binary, Input, Output or Random. We use Output because we will be writing data to the file. Filenumber is the unique file identifier I mentioned above. Note that we used the FreeFile function to generate this number. Again, for more information on Open, see the VB Help file. The Print # statement takes two pieces of information: the file number, and the text to be written.

With this code now in place, try running HTMLExport again. After clicking the OK button from the UserForm, the .htm file specified in the form will be created. You should be able to double-click on this file in Windows Explorer to open it up in your browser, and you should see the just the title you entered in the UserForm.

Next we’ll fill out the GenerateHTML function with further Print # statements, but this time writing out actual HTML tags. We’ll start by replacing the current single Print # statement with:

Print #iFileNum, "<HTML>"
Print #iFileNum, "<TITLE>" + sTitle + "</TITLE>"
Print #iFileNum, "<BODY>"
Print #iFileNum, "<P><B><FONT SIZE=5>" + sTitle + "</FONT></B></P>"

Next, we’ll write the actual data from the current worksheet, by looping through each row and column in the worksheet’s UsedRange. The HTML tags used to create a table are <TABLE> for the table itself, <TR> for each table row, and <TD> for each table cell.

Dim lRowCount As Long
Dim lRow As Long
Dim iColCount As Integer
Dim iCol As Integer

lRowCount = ActiveSheet.UsedRange.Rows.Count
iColCount = ActiveSheet.UsedRange.Columns.Count
Print #iFileNum, "<TABLE BORDER=1>"
For lRow = 1 To lRowCount
  Print #iFileNum, "<TR>"
  For iCol = 1 To iColCount
    Print #iFileNum, "<TD>"
    Print #iFileNum, CStr(Cells(lRow, iCol).Value)
    Print #iFileNum, "</TD>"
  Next iCol
  Print #iFileNum, "</TR>"
Next lRow
Print #iFileNum, "</TABLE>"

Then, add two more lines to close out the remaining open tags…

Print #iFileNum, "</BODY>"
Print #iFileNum, "</HTML>"

Make sure the last line of code in the subroutine is:

Close iFileNum

Give it a try, but before you run HTMLExport again, be sure you have a worksheet active which contains some actual data. You can either type some values into the sheet you already have open, or open another Excel file you’ve previously saved. Voila! You’ve now turned your Excel data into HTML!

Windows API Functions

If I haven’t lost you yet, we still have two more matters to take care of, and we will use Windows API functions for each: the Browse button on our UserForm, and automatically launching the browser after generating the HTML data. API is an acronym standing for “Application Programmer Interface”. The Windows API is basically a set of functions which lets you work directly with the operating system. This is somewhat of an advanced topic, especially since these functions are really set up to be called from C code, not VB, so I’m not going to walk through it, but instead provide you with code which you can copy right into your project.

The code consists of declarations for a couple of complex structures (aka Types), a constant value, and two API functions called ShellExecuteEx and GetSaveFileName. Following the declarations are two “wrapper functions”, basically simplified VB-friendly versions of the two API functions. These wrappers handle setting up the necessary structures and calling the API functions. You will call the wrapper functions from your code rather than directly calling the API functions. Click HERE to see/copy the code.

In the VB Editor, double-click on HTMLExportForm in the Forms folder in the Project window. Double-click the Browse button to create a subroutine called btBrowse_Click. Here we will call the API wrapper function VBGetSaveFileName. This function takes, as a parameter, the default file name, and returns the file entered/selected by the user. We want to use the current contents of our file name text box as the default, and replace the contents of that text box with whatever file name the user enters. So we need just a single line of text:

Private Sub btBrowse_Click()
  txtFileName.Text = VBGetSaveFileName(txtFileName.Text)
End Sub

Launching the HTML Page

To launch the new HTML page in your web browser, we will use the wrapper function VBShellExecute. ShellExecute basically just tells your operating system to open up the specified file in whatever application you happen to have associated with files of that type. This is the same as double-clicking on a file. We’re not explicitly calling up your browser, but rather assuming that files of type .htm are already associated with a browser.

All we need to do is call VBShellExecute after the HTML file has been generated, and pass it the name of the file to open. However, recall that we added a checkbox to the UserForm which allows the user to specify whether or not they want to view the page in the browser. So we’ll need to check the value of that checkbox before opening the file. We’ll do this inside the HTMLExport function.

Sub HTMLExport()
  Dim myform As New HTMLExportForm
   myform.Show 1
   If myform.Result Then
  Dim iFileNum As Integer
    GenerateHTML myform.txtFileName.Text, myform.txtTitle.Text
      If myform.xbView.Value = True Then
        VBShellExecute myform.txtFileName.Text
      End If
    End If
End Sub

That’s it folks. I’ve attached the completed project here for you to download: HTMLExportProject.xls (zip format). Of course, there is a lot more you could do with this project if you’re feeling ambitious, by adding controls to the UserForm which will allow the user to further customize the appearance of the HTML page (gridlines, colors, fonts, etc.)

If you have any questions or comments, feel free to drop me an e-mail: thorpe@ntsource.com. Good luck!