Section 3.10. Program with XML Maps

3.10. Program with XML Maps

The preceding sections explained how to use the new XML features found in Excel and provided code for saving, transforming, importing, and exporting XML with Excel. Those sections provide a context for Excel's XML features and explain programming tasks that surround those features. The rest of this chapter deals exclusively with the XML objects Excel provides and offers specific examples of programming tasks you can perform with those objects, properties, and methods.

Excel's XML object model deals exclusively with XML maps. Opening and saving XML spreadsheets is done through the Workbook object's Open and Save methods. Figure 3-20 illustrates the Excel XML objects hierarchically.

Figure 3-20. The XML object hierarchy

The XmlMap object allows you to perform the following tasks in code:

• Add XML maps to a workbook

• Delete XML maps from a workbook

• Export XML data through an XML map

• Import XML data through an XML map

• Bind an XML map to an XML data source

• Refresh mapped lists and ranges from an XML data source

• View the XML schema used by an XML map

The following sections explain these tasks in greater detail.

The XML Source task pane only gets you so far. By using code, you can create ways to update XML maps, change data bindings, and automate any other tasks that need automating.

3.10.1. How to add or delete XML maps

Use the XmlMaps collection to add or delete XML maps in a workbook. The Add method takes the location of an XML schema as its first argument, and when Excel adds an XML map to a workbook, it copies the contents of that schema into the workbook. For example, the following line creates a new XML map using the SimpleOrder.xsd schema file:

      ThisWorkbook.XmlMaps.Add (ThisWorkbook.path & "\SimpleOrder.xsd")

If you substitute an XML source file for the XML schema, the Add method will infer a schema from the XML source. As noted earlier, inferring a schema can omit some nodes from the resulting XML map.

When Excel creates a new XML map, it names the map using the name of the root node and appending _Map. A number is added to the name if a map with that name already exists. For example, the preceding line of code creates a map named "Orders_Map" the first time it runs, "Orders_Map2" the second time, and so on.

Use the XmlMap object's Delete method to remove a map from a workbook. The following code deletes the map named "Orders_Map":

      ThisWorkbook.XmlMaps("Orders_Map").Delete

If you use the Delete method on a map that is currently used to import data to a list, Excel simply deletes the map and disables the Refresh XML Data task for that list. Excel does not warn you as it does when you delete a map through the user interface.

3.10.2. How to export/import XML

Use the XmlMap object to import or export XML from code. For example, the following line imports an XML file through an existing XML map into a workbook:

      ThisWorkbook.XmlMaps("Order_Map").Import (ThisWorkbook.Path & "\1002.ord")

Similarly, the XmlMap object's Export method exports XML data from a workbook. The following code exports data through an existing XML map:

      ThisWorkbook.XmlMaps("Order_Map").Export ThisWorkbook.Path & "\1002.ord"

Use the ImportXml and ExportXml methods to import or export XML as a string variable rather than as a file. For example, the following code displays the contents of a list mapped using the Order_Map as XML in the Debug window:

      Dim xmap As XmlMap, xml As String, res As XlXmlExportResult      Set xmap = ThisWorkbook.XmlMaps("Order_Map")      res = xmap.ExportXml(xml)      Debug.Print xml

3.10.3. How to refresh/change/clear data

Use the Databinding object's Refresh method to refresh a list that was linked to XML data through an XML map. The Refresh method is equivalent to clicking the Refresh XML Data button on the List toolbar.

You can use the Databinding object's LoadSettings method to change the data source used by the XML map. When combined, the LoadSettings and Refresh methods are equivalent to calling the XmlMap object's Import method. The advantage of combining LoadSettings and Refresh is that changing the data source and refreshing the list are handled in separate steps:

      Dim xmap As XmlMap, res As XlXmlExportResult      Set xmap = ThisWorkbook.XmlMaps("Order_Map")      ' Change the data source.      xmap.DataBinding.LoadSettings (ThisWorkbook.path & "\2002.ord")      ' Refresh the list from the data source.      res = xmap.DataBinding.Refresh

3.10.4. How to view the schema

You can get the schema used by an XML map through the Schemas collection. Each XML map has one schema. You can't add or delete schemas through the Schemas collection.

Use the Schema object's Xml method to return the schema used by an XML map. The Xml method returns the schema without whitespace, so you will want to use a formatting helper function, such as PrettyPrint, when displaying the schema:

      Dim xmap As XmlMap, xsd As String      Set xmap = ThisWorkbook.XmlMaps("Order_Map")      xsd = xmap.Schemas(1).xml      Debug.Print PrettyPrint(xsd)

PrettyPrint is shown here for your reference:

      Function PrettyPrint(xml As String) As String          ' Requires reference to the Microsoft Office SOAP type library          Dim rdr As New SAXXMLReader, wrt As New MXXMLWriter          Set rdr.contentHandler = wrt          wrt.indent = True          rdr.Parse (xml)          PrettyPrint = wrt.output      End Function