Home	Excel	Access
| Simple MakeHTML | Saving XML files |

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 may be achieved by generating a series of 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 spreadsheet 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 macro is available for data which is to be converted into a plain table. This will make a more compact file.

The macro is intended to be incorporated within a spreadsheet which is occasionally converted and published. Instead of presenting the user with a series of dialog boxes into which they can enter the settings each time the macro is run, these settings are declared as variables within the code of the macro itself. Therefore, although only a very limited knowledge of VBA is required, this solution is not appropriate to all users.

Setting up the macro

Open the Excel spreadsheet that you wish to publish. Open the VBA editor and paste the code into a new module. The MakeHTM procedure should then be visible in your list of macros.

Select and edit the procedure.

Would you like to learn more?

Excel 2010 Power programming with VBA 

• An array called 'MyFormats' needs to be amended. Each array element represents the number format for a column. They can contain number or date formats or be left as "" for text with no format definition. For example "#,##0; (#,##0)" generates 1,234 or (1,234), "dd mmm yy" generates 25 Dec 02, "0.00%" generates 12.34%.
• Specify the DefaultFontSize variable to show the normal font size used on your spreadsheet.
• PageName sets the location and name of the HTML file to be saved.
• Set the numbers for FirstRow, LastRow, FirstCol and LastCol to specify the cell range to be converted. Columns are specified as numbers where A=1, B=2 and so on.
• Enter a cell location to contain a hyperlink to the newly converted page.
• Edit any header narrative for your page which you wish to precede the converted range.

Return to your worksheet and run the MakeHTM macro.

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.

• Unlike other similar products, 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.
• 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 range.
• Conditional formatting is not recognised by this program.
• Number and date formatting applies to a whole column. Therefore to show a value in a different format may require it to be converted into 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.