Excel 2003 Programming: A Developer's Notebook

Get an XML Map from a List or Range

Use the XPath object to get or set the data binding used by a list column or a range. Figure 3-21 shows the relationship between these objects.

Figure 3-21. Getting an XML map from a list column or range

You can use the XPath object to add or remove bindings to list columns or ranges as described in the following sections.

How to bind XML to a list column

Use the XPath object’s SetValue method to bind data from an XML map to a list column or range. SetValue allows you to dynamically create lists from an XML map. For example, the following code creates a new list, adds three columns to that list, and binds each column to a different node in an XML map:

     Set ws = ThisWorkbook.Sheets("Mapped List")
     Set xmap = ThisWorkbook.XmlMaps("Numbers_Map")
     ' Create a list object.
     Set lo = ws.ListObjects.Add(xlSrcRange, [A3])
     ' Add a column to the list.
     Set lc = lo.ListColumns.Add
     ' Map the column to an element in an XML map.
     lc.XPath.SetValue xmap, "/Numbers/Number/One", , True
     ' Repeat for two more columns.
     Set lc = lo.ListColumns.Add
     lc.XPath.SetValue xmap, "/Numbers/Number/Two", , True
     Set lc = lo.ListColumns.Add
     lc.XPath.SetValue xmap, "/Numbers/Number/Three", , True

How to remove a binding

Use the XPath object’s Clear method to remove a binding from a list column or range. For example, the following code removes the bindings from the list ...