Creating an XML Mapping Schema in Excel 2010

Create the XML Data File

In this task, you create an XML data file in a text editor that is used later in this exercise.

To create the XML data file

1. Start the text editor such as Notepad.
2. Paste or type the following XML into the editor.
   XML

   <?xml version='1.0'?>
   <BookInfo>
      <Book>
         <ISBN>989-0-487-04641-2</ISBN>
         <Title>My World</Title>
         <Author>Nancy Davolio</Author>
         <Quantity>121</Quantity>
      </Book>
      <Book>
         <ISBN>981-0-776-05541-0</ISBN>
         <Title>Get Connected</Title>
         <Author>Janet Leverling</Author>
         <Quantity>435</Quantity>
      </Book>
      <Book>
         <ISBN>999-1-543-02345-2</ISBN>
         <Title>Honesty</Title>
         <Author>Robert Fuller</Author>
         <Quantity>315</Quantity>
      </Book>
   </BookInfo>
   

3. Save the document as C:\BookData.xml.
4. Close the text editor.

Create the XML Mapping Schema File

In this task, you create a custom XML Mapping schema by using Microsoft Visual Basic for Applications (VBA). You can create a schema with either of two methods: by explicitly writing the template XML in the code or by reading the XML file into a string and then creating the schema from that string.

To create the XSD in Excel by explicitly writing the XML in the code

1. Create a blank workbook in Excel 2010.
2. Open the Visual Basic Editor by pressing Alt+F11.
3. Insert a general module by clicking Insert and then clicking Module.
4. In the module, paste or type the following code.
   VBA

   Sub Create_XSD()
      Dim StrMyXml As String, MyMap As XmlMap
      Dim StrMySchema As String
      StrMyXml = "< BookInfo >"
      StrMyXml = StrMyXml & "<Book>"
      StrMyXml = StrMyXml & "<ISBN>Text</ISBN>"
      StrMyXml = StrMyXml & "<Title>Text</Title>"
      StrMyXml = StrMyXml & "<Author>Text</Author>"
      StrMyXml = StrMyXml & "<Quantity>999</Quantity>"
      StrMyXml = StrMyXml & "</Book>"
      StrMyXml = StrMyXml & "<Book></Book>"
      StrMyXml = StrMyXml & "</ BookInfo >"
   
      ' Turn off async loading.
      Application.DisplayAlerts = False
      ' Add the string to the XmlMaps collection.
      Set MyMap = ThisWorkbook.XmlMaps.add(StrMyXml)
      Application.DisplayAlerts = True
   
      ' Create an empty file and output the schema.
      StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
      Open "C:\MySchema.xsd" For Output As #1
      Print #1, StrMySchema
      Close #1
   End Sub
   

5. Close the Visual Basic Editor.
6. Next, run the code. On the Developer tab, click Macros, highlight Create_XSD, and then click Run. Examine the schema file at C:\MySchema.xsd. Shortly, you will load this file into Excel to create an XML map.

To create the schema in Excel by reading the XML data into a string

1. In a blank Excel 2010 workbook, open the Visual Basic Editor by pressing Alt+F11.
2. Insert a general module by clicking Insert and then clicking Module.
3. In the module, paste or type the following code.
   VBA

   Sub Create_XSD2()
      Dim StrMyXml As String, MyMap As XmlMap
      Dim StrMySchema As String
      ' Book.xml is the file created in section one of this topic.
      StrMyXml = "C:\BookData.xml"
   
      ' Turn off async loading.
      Application.DisplayAlerts = False
      ' Add the string to the XmlMaps collection.
      Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
      Application.DisplayAlerts = True
   
      ' Create an empty file and output the schema.
      StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
      Open "C:\BookData2.xsd" For Output As #1
      Print #1, StrMySchema
      Close #1
   End Sub
   

4. Close the Visual Basic Editor.
5. Next, run the code. On the Developers tab, click Macros, highlight Create_XSD, and then click Run. Examine the schema file at C:\MySchema.xsd. Shortly, you will load this file into Excel 2010 to create an XML map.

Import the Schema and XML Data into Excel

In this task, you import the schema created in the previous section into Excel to create an XML map and then import an XML data file.

To create the Xml Map and then import the XML data file into Excel

1. On the Developer tab, in the XML group, click Source to open the XML Source task pane.
2. On the task pane, click Xml Maps.
3. In the Xml Maps dialog box, click Add, navigate to the file BookInfo.xsd, click Open, and then click OK.
4. In the XML Source task pane, drag the BookInfo node to cell A1. This action creates a table in the worksheet as shown in Figure 1. You can format the table as necessary.
   Figure 1. Table created from the Xml Map
5. Next, import the XML data file. On the Developer tab, click Import.
6. In the Import XML dialog box, navigate to the XML data file and then click Import. The data is mapped into the formatted table as shown in Figure 2.
   Figure 2. XML data is imported into the table