Creating an XML Mapping Schema in Excel 2010

Article
04/28/2015

Office Quick Note banner

Getting Started with Excel 2010 Extensibility: Learn how to create and utilize Xml Mapping schemas in Excel 2010.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Create the XML Data File
Create the XML Mapping Schema File
Import the Schema and XML Data into Excel
Next Steps

Published: November 2010

In this exercise, you create a sample XML data file. You then create an XML Mapping schema by using one of two methods. You then use that schema to create a table mapped to the sample XML data. To complete this task, you must do the following:

Create the XML Data File
Create the XML Mapping Schema File
Import the Schema and XML Data into Excel

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

Start the text editor such as Notepad.

Paste or type the following XML into the editor.

<?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>

Save the document as C:\BookData.xml.
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

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

        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

Close the Visual Basic Editor.
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

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

        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

Close the Visual Basic Editor.
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

On the Developer tab, in the XML group, click Source to open the XML Source task pane.
On the task pane, click Xml Maps.
In the Xml Maps dialog box, click Add, navigate to the file BookInfo.xsd, click Open, and then click OK.
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
Next, import the XML data file. On the Developer tab, click Import.
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