You first need to create an XSD schema using your favorite XML editor
(I use XML Spy, which is fantastic), or a text editor like Notepad.
For example, given data on a worksheet like
Groups Number Tens Hundreds
One 1 10 100
Two 2 20 200
Three 3 30 300
Four 4 40 400
I created a schema named TestSchema.xsd as shown below:
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" attributeFormDefault="unqualified"
targetNamespace="
http://www.cpearson.com/test"
xmlns:test="
http://www.cpearson.com/test">
<xs:element name="Groups">
<xs:complexType>
<xs:sequence>
<xs:element name="Group" minOccurs="1"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Title"></xs:element>
<xs:element name="Number"></xs:element>
<xs:element name="Tens"></xs:element>
<xs:element name="Hundreds"></xs:element>
</xs:sequence>
</xs:complexTyp
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Then, on the Developer tab in Excel, click the Source button to open
the XML Source task pane. At the bottom of that, click the XML Maps
button and in that dialog, click Add to open your schema xsd file and
click OK. Now, click the top-most element in the XML tree (labelled
Groups in this example), and drag that item to the upper left corner
of the data on the worksheet ("Groups" in the worksheet example
above). Excel will format the table with lots of pretty colors that
you can get rid of by unchecking the "banded rows" checkbox on the
Table Style Options panel. Now, go back to the Developer tab,click
Export, and specify the name of the xml file to which you want to save
the data.
As often as not, I end up just using VBA input/output procedures to
write the raw XML directly to a file rather than jumping through
Excel's hoops.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com