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
- Download the Excel 2003 XML Tools Add-in, and then follow the instructions.
- Open Excel 2010, click on File > Options, select the Add-Ins category.
- In the Manage box down at the bottom, click Go.
- 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:
- Make sure the XmlTools check box is selected in the Add-Ins available list, and then click OK to load the add-in.
- 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.
Steps to convert the data in Excel to an XML file
- Enter the data that needs to be converted into Excel in a tabular format.
- On the Add-ins tab, click the arrow next to XML Tools, and then click Convert a Range to an XML List.
- Enter the cell range of the data by clicking and dragging
- Determine if the first row contains data and make the right choice.
- 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.
- Click the Developer tab, in the XML group, click Export.
- Type a name for the XML data file and click Export.
- 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!