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!