XML Export from Excel – part 1

I thought I’d look at using Excel as an information-gathering front-end for XML-based data, which can then feed into production of documentary reports (HTML, PDF). Or put the other way round, a reporting facility for Excel.

Here’s a simple scenario. My company has various Business Units (BUs) which produce and sell various products. Every Quarter, each BU produces a Sales/Costs report as an Excel workbook. Something like this:

The basic data is in the blue ProductTable. The pink cells are simple totals (=SUM(ProductTable[Sales]), etc, using structured table references). Incidentally, I should have the period as 2009Q4, etc, so that values sort lexically (2009Q4, 2010Q1, …).

To export this data as XML, we need to create a map from worksheet cells to XML schema elements. This is what the schema looks like in the Excel XML Source pane:

Each element is mapped to the appropriate cells by dragging-and-dropping. The root element is <bureport>.

Now, we could just click  >Developer >XML >Export. But there are a couple of reasons why we might want to do it from code. Firstly, each period, we will be receiving a report from each BU, and it would be nice to do the export as a batch operation.

Secondly, the XML map inside Excel seems to not keep a reference to the schema from which it was created. This means that when the XML is exported the root element comes out as just <bureport>, whereas we want it to have a reference to the schema:

<bureport xmlns:xsi=’http://www.w3.org/2001/XMLSchema-instance&#8217; xsi:noNamespaceSchemaLocation=’bureport.xsd’>

where the critical bit is the filename at the end: bureport.xsd. For simplicity, this is assumed to be in the same directory as the bureport XML file.

Maybe I’m missing something here, but it seems odd that Excel internalizes the schema, but doesn’t have an option to associate a schema location with a map, and then generate a root element with that location, as above, in exported files.

Now, to begin with, I’m going to ignore the batch-operation over multiple workbooks, because it should be straightforward. So the exporting code is going to go into the bureport workbook (template) itself, and I’ll just run it directly from each workbook, from the VBA side.

The code needs to:

• construct the output file name from the BU name and period (e.g. Report_Applications_4Q2009.xml)
• open a TextStream on this (new) file
• get the complete XML string from the bureport map
• substitute the basic <bureport> element with the full one (see above)
• write the modified XML string to the TextStream.

Code tomorrow.