A macro to make an HTML page from an Excel worksheet (advanced)

A visual basic macro

Excel contains a command to save an area of a worksheet in HTML format so that it can viewed through a web browser and incorporated in a website. The files created by this command are generally a faithful representation of the original worksheet but this process may involve the creation of a series of supplementary files and folders.

Alternatively a macro can be used to create a web page which contains plenty of formatting and can be user defined to convert a particular range of a worksheet. If the page is to be regularly updated and published the macro can be linked to a button placed on your worksheet.

A worksheet such as this can be converted into HTML code (a web page) and inherit the following features:

Create web page tables with expanding or drill down sections

Find out more

• font colours
• font sizes
• bold text
• background colours
• borders (with styles and colours)
• column widths
• number and date formatting
• text alignment
• merged cells

To view the converted page, click here.

A simpler version of the program is available for data which is to be converted into a plain table. This will make a more compact file.

The program is intended to be incorporated within a spreadsheet which is occasionally converted and published. The range to be published is defined by a named table range. This needs to be declared in Excel before the program is run or assigned to a button.

Setting up the program

Open the Excel spreadsheet that you wish to publish. Open the VBA editor and paste the code into a new module. You may wish to edit the program and amend the DefaultFontSize variable to reflect the normal font size used on your spreadsheet.

Create a new procedure to call the MakeHTable procedure along with the required parameters.

Sub CreateFinStmt()
' Run the MakeHTable program to recreate one or more excel tables
' The required parameters are (1) table name (2) file location to save (3) filename
 MakeHTable "finstmt", "C:\mydata\", "J1_finstmt.htm"
End Sub

The new procedure name (CreateFinStmt in this example) should then be visible in your list of macros and can be linked to a button.

Notes about the conversion

The output HTML file may not be a faithful reproduction of your original. Some of the defects can be resolved by amending your spreadsheet.

• All Font and Background colours should be recognised and converted.
• Font sizes are not faithfully reproduced. If the font size in a cell differs from the specified DefaultFontSize, the HTML page will show it as a single size bigger or smaller.
• Different font names are ignored.
• Borders can be in one of two styles - normal solid or a dashed line. Note that to include borders the adjacent cells on both sides of a border must be within the converted table range.
• Conditional formatting is not recognised by this program.
• Number and date formats should be replicated. If the program fails to recreate a particular format, you could convert the cell contents into formatted text on your worksheet using the TEXT function.
• Cells can only be shown as merged horizontally, not vertically.

The macro was written by Chris Mead.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file

W3Schools provides a collection of free HTML, CSS, JavaScript, DHTML, XML, XHTML, WAP, ASP, SQL tutorials with lots of working examples and source code. I have found this to be the most useful site for learning about anything web-related.