XML and Excel

Hi everyone,

I have had a Dutch article on XML and Excel on my site for quite some time now, but never got round to translating the thing into English.

Well, yesterday I found a bit of time during a long train-ride, so here it is:

XML And Excel

From the intro:

Microsoft Office 2003 Professional was the first Office version that took the XML standard seriously. The XML standard has been devised to ease the markup of data (especially on the web). A well known example of the use of XML are RSS feeds with which one can gather news from web pages. In reality, these so-called RSS-feeds are nothing less than XML files. This article introduces XML and shows some things that can be done with it (specifically in Excel).


Jan Karel Pieterse

Posted in Uncategorized

9 thoughts on “XML and Excel

  1. Note that the XML tools are available in ALL editions of Office 2007. After all the fanfare of the new XML stuff in Excel 2003 and the unprecedented (not to mention anal) step by Microsoft to limit the tools to only certain editions of Excel (thus guaranteeing its failure), Microsoft didn’t even bother to update (or build in) the XML add-in tools they provided after Office 2003 shipped. Even an obvious task, like exporting a table as XML, isn’t supported.

  2. Hi JKP,

    Thanks for the article and the files…

    I have gone through this in detail and a few more articles in the reference sections..
    I also found an interesting Addin on the MS website called XML tools- which lets you convert a Range in to an XML data..may be you should include it as a reference as well

    I am wondering in the context of Excel-XML we are using it to Pull Data from a file.
    Is it also possible to push data in to another existing file (without opening it)

    Also we have been doing the above operations via ADO/VBA so far, does the XML way offer advantages…

  3. I don’t know if you can add data to an existing XML file (and something must always open the file, otherwise data can’t be written to it) using Excel. I do know Excel has several limitations regarding what it can export to XML regarding the XML structure.

    Excel can only export simple tables, it cannot cope with more complex structures (like the companies example I use actually). Exporting hierarchically ordered XML data is thus something you would not be using Excel for.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.