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 excessively large and contain limited formatting.

Alternatively a macro can be used to create a web page which contains better 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:

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

To view the converted page, click here. The HTML code includes some Cascading Style Sheet (CSS) instructions including a suitable format for displaying a table. For the purposes of a comparison, the equivalent (larger) file created using the Excel menu option 'SaveAsHTML' is also linked here.

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

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

Setting up the macro

Open your Excel spreadsheet that you wish to publish. Open the VBA editor and import a file containing 'MakeHTMLadvanced.bas' (or whetever name you used to save the macro text file). Alternatively copy and paste the code into a new module. The MakeHTM procedure should then be visible in a module.

Select and edit the procedure.

Would you like to learn more?

Excel 2003 Power programming with VBA (with CD)

• 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.