Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Creating an XML Mapping Schema in Excel 2010

Office 2010

Office Quick Note banner

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

Last modified: May 09, 2011

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:

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

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.

    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.

    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.

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

    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

    XML data is imported into the table
Show:
© 2015 Microsoft