Programming Excel with VBA and .NET

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.