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.


9 Responses to “XML Export from Excel – part 1”

  1. 1 james August 9, 2011 at 12:56 pm

    can you help with a system to convert Excel xls file to xml

  2. 2 james August 9, 2011 at 12:57 pm

    can you help wti converting Excel xls file into xml file

  3. 4 Amer Chebaro March 28, 2012 at 6:02 pm

    Is there a way to export multiple XML files from 1 excel document.
    In other terms, can each mapped line from the excel document generate an xml document VS all the excel lines generating xml code in one big xml doc?

    Thank you

  4. 5 Jodie June 17, 2013 at 3:34 pm

    If it can be just you, otherwise you and your spouse,
    you may well be better off using a single cup coffee machine (don’t be concerned, making two cups will simply take a minute). Even should you’re outdoors
    and you have is a campfire, it is possible to still have your chosen brewed coffee instead
    in the instant mixes. Its versatility and functionality
    has made it the “most used” espresso maker in workplaces,
    exceeding 200,000 office installations in North America.

  5. 6 medical August 10, 2013 at 10:47 am

    Hey there fantastic blog! Does running a blog like this take a great
    deal of work? I’ve absolutely no understanding of coding but I had been hoping to start my own blog in the near future. Anyways, should you have any ideas or tips for new blog owners please share. I understand this is off topic however I simply had to ask. Thanks!

  6. 7 moja strona August 21, 2014 at 3:04 pm

    Świetnie się przegląda twoje wpisy. Liczę na więcej!

  7. 8 jual madu asli June 1, 2016 at 3:46 am

    Magnificent beat ! I would like to apprentice while you amend your site, how can i subscribe for a blog
    web site? The account helped me a acceptable deal.
    I had been a little bit acquainted of this your broadcast offered bright clear idea

  1. 1 XML Export from Excel – part 2 « Roy MacLean's VBA Blog Trackback on February 1, 2010 at 5:16 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

January 2010
« Dec   Feb »

%d bloggers like this: