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

convert a worksheet to an html file using an Excel macro

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

excel web page

Find out more

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.

The program is saved as a text file and can be opened here. [Program was updated March 2015]

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.

The macro was written by Chris Mead.

Get this information as a document
accompanied by Excel worksheets
The document is in PDF format Click here for details about
obtaining this file

learn html 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.

file: xladvhtml.htm 2016 Last updated Mar15 CMIDX S5 P1 Y