Section 3.11. Get an XML Map from a List or Range

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

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

3.11.2. 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 created in the preceding section:

      Set ws = ThisWorkbook.Sheets("Mapped List")      Set lo = ws.ListObjects(1)      For Each lc In lo.ListColumns          lc.XPath.Clear      Next