Importing XML Data into a Mapped List in Excel 2010

  • Article

Office Quick Note banner

Getting Started with Excel 2010 Extensibility: Learn how to import XML data into a list in Excel 2010, with and without a mapping schema.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Import XML Data and the XML Schema to Create the Mapped List
Import XML Data into an Existing Mapped List
Import XML Data without a Schema Map
Next Steps

Published:   November 2010

Provided by:    Frank Rice, Microsoft Corporation

In this exercise, you programmatically import XML data into a list in Microsoft Excel 2010. Three ways of accomplishing this are discussed. To complete this task, you must do one or more of the following:

  • Import XML Data and the XML Schema to Create the Mapped List

  • Import XML Data into an Existing Mapped List

  • Import XML Data without a Schema Map

Note

This topic assumes that you already have the XML data file and the XSD schema file referred to in this topic. To create those files, see the Quick Note titled Creating an XML Mapping Schema in Excel 2010.

Import XML Data and the XML Schema to Create the Mapped List

In this task, you first import an XSD schema file in Excel 2010 to create an XML map. You then create a new list. And finally, you use XPath statements to map the XML data to the list.

To create a mapped list by using XML data

  1. Open a blank workbook in Excel.

  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 CreateXMLList()
   Dim oMyMap As XmlMap
   Dim strXPath As String
   Dim oMyList As ListObject
   Dim oMyNewColumn As ListColumn

   ' Add a schema map.
   ' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
   ThisWorkbook.XmlMaps.Add ("C:\BookData.xsd")

   ' Identify the target schema map.
   Set oMyMap = ThisWorkbook.XmlMaps("BookInfo_map")

   ' Create a new list in A1.
   Range("A1").Select
   Set oMyList = ActiveSheet.ListObjects.Add

   ' Find the first element to map.
   strXPath = "/BookInfo/Book/ISBN"
   ' Map the element.
   oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath

   ' Add a column to the list.
   Set oMyNewColumn = oMyList.ListColumns.Add
   ' Find the next element to  map.
   strXPath = "/BookInfo/Book/Title"
   ' Map the element
   oMyNewColumn.XPath.SetValue oMyMap, strXPath

   ' Add a column to the list.
   Set oMyNewColumn = oMyList.ListColumns.Add
   ' Find the next element to map.
   strXPath = "/BookInfo/Book/Author"
   ' Map the element.
   oMyNewColumn.XPath.SetValue oMyMap, strXPath

   ' Add a column to the list.
   Set oMyNewColumn = oMyList.ListColumns.Add
   ' Find the next element to map.
   strXPath = "/BookInfo/Book/Quantity"
   ' Map the element.
   oMyNewColumn.XPath.SetValue oMyMap, strXPath

   ' Give the columns logical names
   oMyList.ListColumns(1).Name = "ISBN"
   oMyList.ListColumns(2).Name = "Title"
   oMyList.ListColumns(3).Name = "Author"
   oMyList.ListColumns(4).Name = "Quantity"

End Sub

  5. Close the Visual Basic Editor.

  6. Next, run the code. On the Developers tab, click Macros, highlight CreateXMLList, and then click Run. The XML is imported into the mapped list as shown in Figure 1.

    Figure 1. List created from the XML data and XSD schema

    List created from the XML data and XSD schema

Import XML Data into an Existing Mapped List

In this task, you programmatically import XML data into an existing mapped list.

To import data into a mapped list

  1. Open the workbook that contains the schema map.

  2. If you have not already done this, create the mapped list. On the Developer tab, in the XML group, click Source. You should see the mapping shown in Figure 2.

    Figure 2. BookInfo_Map map

    BookInfo_Map map

  3. Drag the BookInfo node to cell A1. This creates the blank list.

  4. Open the Visual Basic Editor by pressing Alt+F11.

  5. Insert a general module by clicking Insert and then clicking Module.

  6. In the module, paste or type the following code.

    Sub ImportXmlFromFile()
     ThisWorkbook.XmlMaps("BookInfo_Map").Import ("C:\BookData.xml")
End Sub

  7. Close the Visual Basic Editor.

  8. Next, run the code. On the Developers tab, click Macros, highlight ImportXmlFromFile, and then click Run. The XML is imported into the list as shown in Figure 1.

Import XML Data without a Schema Map

In this task, you programmatically import XML data into a list where Excel creates a default map.

To import the XML data without a schema map

  1. In a blank 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 ImportXMLtoList()
     Dim strTargetFile As String
     Application.DisplayAlerts = False
     strTargetFile = "C:\BookData.xml"
     Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
     Application.DisplayAlerts = True
End Sub

  4. Close the Visual Basic Editor.

  5. Next, run the code. On the Developers tab, click Macros, highlight ImportXMLtoList, and then click Run. The XML is imported into the list as shown in Figure 1.

Next Steps