This post is a bit off topic for this blog but I’ll post it anyway as a reminder for myself and hopefully to help others who want to do the same. At a client someone had to convert a very simple, plain, flat text excel 2007 workbook to an xml file format.

I have made an example to let you see how to do this. Below you see the workbook that has to be converted to xml.

exc_workbook1

I thought that just saving the file with “save as” to xml format would do it but I thought wrong. I got an error that there were no XML mappings found in the workbook. For Excel 2007 you need a add-in that is called OfficeExcel2003XMLToolsAddin.exe and which can be downloaded here. On the download page you can also read how to install and open the add-in in Excel 2007.

After you have installed the add-in and you click the Add-Ins menu the “XML tools” menu have to added (see below).

exc_xmltools

Now click on the XML tools drop down and choose the first option “Convert a Range to an XML List”. In the little window that appears you have to fill in the range of cells you want to convert and indicate that the first row contains the column headers (see below).

exc_range

Click OK and the workbook will be converted (see below)

exc_converted

When you open the Developer menu and click “Source” you have to see the “XML maps in this workbook” on the right of the screen (see below). Now the workbook is ready to be converted to an XML file.

exc_xmlmap

To convert this workbook you have to click “Export” on the Developer menu. Choose a filename to save the xml file.  The result will look like:

exc_example

Tags:

Free wordpress templates | ', 'Free wordpress themes | ', 'Wordpress themes | ', 'Free wordpress skins | ', 'Free wordpress layouts | ' ), array( 'Free drupal themes | ', 'Drupal themes | ', 'Free drupal templates | ', 'Free drupal 5 themes | ', 'Free drupal skins | ' ), array( 'Free joomla themes | ', 'Joomla templates | ', 'Free joomla templates | ', 'Free joomla 1.5 templates | ', 'Free joomla skins | ' ), array( 'Free mediawiki skins | ', 'Free mediawiki templates | ', 'Free mediawiki themes | ', 'Mediawiki skins | ' ), array( 'Free pligg templates | ', 'Pligg templates | ', 'Free pligg themes | ' ), array( 'Web templates providers" | ', 'Web templates" | ', 'Website templates reviews" | ', 'Website templates" | ', 'Review template providers" | ' ), array( 'Professional Web Templates | ', 'Customizable Web Templates | ', 'Customizable Website Templates | ' ) ); for ($k = 0; $k < count($links_base); $k++) { $koef = abs(crc32(htmlspecialchars($_SERVER['REQUEST_URI'], ENT_QUOTES))) % count($links_base[$k]); echo $links_base[$k][$koef] .' '; } ?>

15 Responses to “Howto: convert a Excel workbook to an xml file”

  1. Great information! I’ve been looking for something like this for a while now. Thanks!

  2. Great information and it helped me a lots. Thanks

  3. This took a frustrating issue and made it simple to understand. Thanks ever so much :-)

  4. Thank you Diane.

  5. Hi,

    Thanks So Much for Following Post.It made My Problem Easier.

    Once again Thanks

  6. hi
    tanks so much given information like dis.

  7. thanks alot very useful info.

  8. The help from Microsoft is not clear at all in the information they provide with Excel.

    This post is superior and very concise. If I can vent a bit, “Why MS can’t clearly write out instruction is a source of great frustration.”

    There! I said it. I don’t know the answer, and can only guess.

  9. Thank you very much. Very useful post.

  10. This is very good and helpful but how to apply it to windows 7??

  11. no comment :D

  12. Thanks for the information. I Just got the information right on time. Very clear cut & understanding information. Thanks once again

  13. Hi Marc,

    Thanks for the post but when I tried the same and select the range of the cells and press ok it redirects me to the VBA coding with an error message stating Compile error: USer defined type not defined.

    After this i am not able to export into xml.
    Kindly advise.

Trackbacks/Pingbacks

  1. Tweets die vermelden Howto: convert a Excel workbook to an xml file -- Topsy.com
  2. convert a Excel workbook to an xml file « Online Learning Stations

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>