If you have worked in Microsoft Office products before, you know that these products can save their native files types out to other types of files, such as to HTML format. If you are familiar with building applications in Excel, you also know that Excel comes with a nice macro recorder that, when activated, will record the actions you perform in Excel and will translate those actions into Visual Basic for Applications code. This feature allows you to have routines generated for you that will perform a task.

Based on this information, you could easily conclude that you could turn on the macro recorder and then save a spreadsheet to HTML using the File|Save As menu item. While this solution might sound ideal, it doesn't work that way. There are two problems that will prevent this solution from working. First, Excel 97 does not have an HTML option on the Save As dialog box. An Excel Add-in is required to save a spreadsheet as HTML. Also, the macro recorder will not capture actions performed in the Add-in.

The Internet Assistant Wizard Add-in will convert Excel spreadsheets and charts to HTML and GIF files. Users can then view those pages in a browser. You can install the Add-in when you install Excel 97, but it does not automatically load when you run Excel. The Tools|Add-Ins menu item will display the Add-Ins dialog box that you can use to load and unload Add-Ins. To load the Internet Assistant Wizard, open the Add-Ins dialog box, then check the item. If it is not in the list of available Add-Ins, you can use the Browse button to locate the Add-In file, html.xla. If you search your hard drive and the file is not present, it was not installed with Excel. By default, the file is located at

C:\Program Files\Microsoft Office\Office\Library

The following steps will enable your VBA code, running in Excel, to save spreadsheets and charts as HTML and GIF files. First, create an Excel spreadsheet and/or chart and format it. You may use color, but use simple, solid colors (red, green, and so on) rather than dithered colors or gradients since your browser may have difficulty displaying them. Next, click the Visual Basic Editor button on the Visual Basic toolbar to open the editor. From inside of the Visual Basic Editor environment, select Tools|References. In the References dialog box, select "HTML." This step will create a programmatic reference from your code to the Internet Assistant Wizard. If "HTML" is not present in the list, click the Browse button and navigate the file system to locate and select the file:

<Installation Drive, usually C:>\Program Files\Microsoft Office\Office\Library\html.xla 

Sub CreateWebPage()

    Dim ObjToConvert(1) As Variant
    
    ' Populate the array of objects for HTMLConvert.
    Set ObjToConvert(0) = Sheets("sheet1").Range("a1:f18")
    Set ObjToConvert(1) = Sheets("sheet1").ChartObjects("Chart 1")
          
    ' Create the HTML document.
    Htmlconvert _
	rangeandcharttoconvert:=ObjToConvert, _
        usefrontpageforexistingfile:=False, _
        addtofrontpageweb:=False, _
        useexistingfile:=False, _
        codepage:=1252, _
        htmlfilepath:="C:\Test.htm", _
        descriptionfullpage:="", _
        lastupdate:=Now
End Sub

• rangeandcharttoconvert - a variant array that holds object references to the ranges and charts to convert. If you don't know which ranges and/or charts you want to convert, run through the wizard once using the user interface. Step one of the wizard will allow you to visually select them and will display the cell range(s) and chart name(s). Reproduce that information in code in this procedure.
• htmlfilepath - The path and filename of the file to create. Any GIFs that are created from charts will also be created in the same folder.
• descriptionfullpage - A brief text description that will appear at the top of the HTML page that is created. Note that a bug in the HTML object will generate an error if you do not pass a string to this parameter. You can pass an empty string if you do not want a description to appear.
• lastupdate - Date and time information that will appear at the bottom of the resulting HTML page. You'll normally want to set this parameter to the return value of the VBA Now function.