Wednesday, May 22, 2013

How to convert an Excel 2010 speadsheet to an XML file

At times we need to display some structured data on a Webpage. If you don't have a large amount of data and don't need to regularly query or update the data, it's a lot easier to use an XML file as the data source rather than going through the hassle of using a full database. Especially if the data is in an Excel spreadsheet, you can easily convert it to an XML file.
There are instructions from Microsoft Website on how to do it in Excel 2003, but with Excel 2010, which is what I'm using currently, things are a bit different. Here are the steps on how to convert an Excel spreadsheet to an XML in Excel 2010.

Prerequisite: install the XML Tools Add-in 

  1. Download the Excel 2003 XML Tools Add-in, and then follow the instructions.
  2. Open Excel 2010, click on File > Options, select the Add-Ins category.
  3. In the Manage box down at the bottom, click Go.
  4. In the Add-Ins dialog box, click Browse, locate the XmlTools.xla file, select it and then click OK. By default, this file is stored in the following folder:
    \Office Samples\OfficeExcel2003XMLToolsAddin
  5. Make sure the XmlTools check box is selected in the Add-Ins available list, and then click OK to load the add-in.
  6. Go to Files > Options > Customize Ribbon, check the “Developer” box in Excel 2010. This will guarantee that the Developer tab will be displayed in the main tabs. 
To verify that the add-in is active, click on the Add-ins tab in Excel, make sure that the XML Tools command menu appears.

Steps to convert the data in Excel to an XML file

  1. Enter the data that needs to be converted into Excel in a tabular format. 
  2. On the Add-ins tab, click the arrow next to XML Tools, and then click Convert a Range to an XML List.
  3. Enter the cell range of the data by clicking and dragging
  4. Determine if the first row contains data and make the right choice. 
  5. If you see an error message that says “Compile error: User-defined type not defined”, click OK on the error message, delete 50 from the highlighted Dim XmlDoc statement, which is to change “msxm12.DOMDocument50” to “msxm12.DOMDocument”. Keep pressing F5 or the run button, and delete 50 from all the errors.
  6. Click the Developer tab, in the XML group, click Export.
  7. Type a name for the XML data file and click Export
  8. If an XML schema file (.xsd) is needed, on the Add-ins tab, click the arrow next to XML Tools, and then click Create XSD files for the XML Schema at the active cell. If the compiling error message pops up, delete 50 from all occurrences until all the errors are fixed. Excel generates the XML schema in Notepad. Save the file as an xsd file.

Now your XML file and your schema file are generated and ready for you to use!

8 comments:

  1. Thanks. This error message appears everytime when I open Excel. How to remove it forerver?

    ReplyDelete
  2. Domanda: quando eseguo xml tools: convert range to XML list
    seleziono la tabella e
    yes first row contains data,
    ho l'errore 1b6 : proprietà o metodo non supportati dall'oggetto
    Qualche aiuto?

    ReplyDelete
  3. Problem in using the tool:
    This 1B6 Error Object does Not Support This Property or Method

    Load from origin, XML mapping, my xsd file; I run the mapping.
    I select my entire affected table, tool, as add-in
    XML Tools: Convert a Range to an XML List
    I select the table, flags on - yes the first line contains the data, I have a message:
    data will not be lost, but the list or xml mapped ranges will be converted to normal ranges and this can not be undone. Continue conversion?
    Yes
    and then I get the error 1B6: Property or method not supported by the object

    Any ideas, help, links, information, solution?
    2 or 3 times I managed to get my xml file, and did not show this error.
    I did the conversion on a regular basis with the tool and I exported all in XML.
    Then close everything and reopen Excel and the problem, error 1B6 reoccurs ...

    ReplyDelete
  4. The solutions : http://pixcels.nl/xmltools12/
    Put XmlTools12.xla in \Office Samples\OfficeExcel2003XMLToolsAddin
    like in the point 4. (4.In the Add-Ins dialog box, click Browse, locate the XmlTools.xla file, select it and then click OK. By default, this file is stored in the following folder:
    \Office Samples\OfficeExcel2003XMLToolsAddin)

    ReplyDelete
  5. Export option is graded out how do i export

    ReplyDelete
  6. Mine is a compiler error which ask me to update it to 64bit using ptrSafe. How do I do this?

    ReplyDelete
  7. i get run time error 1004. visible -not supported ?????

    ReplyDelete
  8. If you have tried to install XmlTools written by James Rivera in a 64 bit Excel install you receive an error because this add-in was written for 32 bit Excel installs.

    With a little trial and error I was able to update XmlTools.xla an Excel Add-in written by James Rivera to work with 64 bit Excel. I have tested this with Excel 2010.

    There were three modules that needed to be updated (CreateXmlFiles, GetCellXmlProps, and CreateXsdFiles). The code is to long to post in this forum but I have posted the information on MrExcel.com forums. Search for - SOLVED - Re: Extracting information from XML files using XmlTools Written by James Rivera. https://www.mrexcel.com/forum/excel-questions/750350-extracting-information-xml-files.html#post4733964

    Enjoy!!

    Regards,

    Joe Coyle

    ReplyDelete