An introduction to Excel and XML data files

This section has been rewritten to take account of changes to software and web standards. Excel is now able to save data directly as an XML file and the method I have used (data islands) to incorporate XML data in HTML pages is redundant. Most modern browsers won't do it. I have therefore rewritten my own projects and replaced XML data islands with HTML iFrames.

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.

VBA programs can be used to export data tables from Excel or Access into simple XML files.

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.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file