An introduction to Excel and XML data files

This section offers some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.

Would you like to learn more?

Learning XML [US] [UK]

This site is not a tutorial on XML data tables or XSL. Suffice it to say that XML is a universal standard format for exchanging data between applications while XSL offers the ability to control its presentation (formatting), sorting and display criteria. The data can be viewed using web browsers such Internet Explorer. The data however is separated and independent from the page used to display it. For a very clear introduction to each of these file types visit the W3C schools tutorials for XML or XSL.

Converting an Excel table to XML using a macro

A VBA macro (linked to a button on the worksheet) can convert a table such as that shown in the example (left) into a simple XML file. The file will contain 6 records, each containing 5 fields.

When the procedure is run it prompts you for the output filename; the range of cells containing the field names (i.e.A3:E3 - the column titles in this example); and the data table itself (A4:E9).

To see the VBA program click here. You can Copy the text, open Excel VBA and Paste the selection into a module.

To see the resulting unformatted XML file, click here.

Note that the program formats numeric and date values using a function called FormChk(). You may choose to either remove the function and edit the reference to it in the main procedure, or alternatively enter your own formatting strings.

The program also includes a function called RemoveAmpersands() which replaces the '&' symbol with '+'. Certain characters such as '&' need special instructions to get correctly displayed in a browser.

Converting an Access query to XML

A similar VBA macro will convert an Access query into an XML file. The procedure (called QFN) has 2 parameters, firstly the query name, secondly an option to format the numbers and dates (0=No, 1=Yes). e.g. QFN "cimtest",1 

The program uses existing query field names for the XML field names.

To see the VBA program (which can be copied into an Access Module) click here.

To see the resulting unformatted XML file click here.

Adding XML files to HTML web pages

There are many options if you wish to display the XML data in your web browser - other than as the unformatted files linked above.

Click here for page two .