As discussed earlier, when you import XML data into a worksheet you have the choice to insert the data as an Excel list. When adding XML data to a list, Excel creates a ListObject object to represent the list. The ListObject object is subordinate to the Worksheet object; therefore, all ListObject objects added to a worksheet are returned as a collection via the ListObjects properties of the Worksheet object. Individual ListObject objects can be accessed from the ListObjects collection.
Dim lstObjects as ListObjects Dim lstObject As ListObject Set lstObjects = ActiveSheet.ListObjects Set lstObject = lstObjects(l)
Each XML data set that has been mapped to a list is represented by a ListObject object. The ListObject object provides an easy path to the range of cells mapped to an XML document. Use the Range property of the ListObject object to return the Range object representing these mapped cells. To return the range representing the insert row for a list (that's the row with the asterisk, see Figure 8.5), use the InsertRowRange property. Please note that the active cell(s) must be within the ListObject object's range or the InsertRowRange property will fail.
Dim IstObject As ListObject Dim insertRow As Range
Set IstObject = ActiveSheet.ListObjects(l)
'If list is not active then activate its range.
If Not IstObject.Active Then IstObject.Range.Activate End If
Set insertRow = IstObject.InsertRowRange
To ensure the ListObject object's range is active, the Boolean value returned by the Active property of the ListObject object is tested in a conditional statement. The ListObject object's range is activated with the Activated method of the Range object. This allows you to set the Range object returned by the InsertRowRange property of the ListObject object. It is now a simple matter to add new data to the list. For example, if the data is mapped to two columns that include a name and number, you can add new data as follows:
insertRow.Cells(1, 1).Value = "Duane Birnbaum" insertRow.Cells(1, 2).Value = 5
Here I use the Cells property of the Range object to return the first cell in the first two columns of the range represented by the variable insertRow.
If the data in an Excel list has been mapped to XML data, you can access the resulting XmlMap object via the XmlMap property of the ListObject object.
Set myMap = ActiveSheet.ListObjects(1).XmlMap
Now you can invoke all the properties and methods of the XmlMap object that were discussed earlier.
Was this article helpful?