Using this
method, we can read in any changes to the XML file that our data range is mapped to.
Before we begin, let??™s copy our GetXMLData function into a standard code module in a new
workbook and save it. Run the GetXMLData macro from the Developer ribbon.
Open the cds.xml file and add a new title to the list by adding the following set of nodes at
the top of the XML file:
Nirvana
Greatest Hits
2000
Save the file. Add the following function to the standard module:
Sub RefreshXML()
Range("A1").Select
ActiveWorkbook.XmlMaps("cds_Map").DataBinding.Refresh
End Sub
Run the code by pressing the F5 key with the insertion point inside the RefreshXML subroutine.
The data range is expanded to include the new data, as shown in Figure 3-12.
Figure 3-12. XML data refreshed
Remove the new set of nodes and run the RefreshXML method again, and the table will
be resized to display only the data from the XML file.
CHAPTER 3 n USING XML IN EXCEL 2007 105
Excel 2007 provides other methods to remove and load XML map settings. The
DataBinding.ClearSettingsmethod unbinds the data in a range from the XML file.
ActiveWorkbook.XmlMaps("cds_Map").DataBinding.ClearSettings
The DataBindings.LoadSettings method takes the file path or URL as an argument to load
the mapping from the specified XML file.
ActiveWorkbook.XmlMaps("cds_Map").DataBinding.LoadSettings ??
"C:\projects\Excel\cds.xml"
Appending XML Data
Both the Excel Workbook object and the XmlMaps collection contain methods for appending or
overwriting XML data in a workbook.
Pages:
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127