Appending XML Data

Both the Excel Workbook object and the XmlMaps collection contain methods for appending or overwriting XML data in a workbook. The Workbook.XmlImport method and the Xmlmaps. Import method provide functionality to do either. Both methods take Overwrite arguments, which when set to False will append data to any existing data. The XmlImport method, however, will not append data when Overwrite is set to False if the optional Destination argument is used. In this case, nothing will happen (the append is cancelled).

We're going to append data from another XML file containing more discography information that we've received. We'll use the ActiveWorkbook's XmlImport method to do the append. Add a function to a standard module and name it AppendXMLData. The code looks like this:

Sub AppendXMLData() Dim map As XmlMap

Set map = ActiveWorkbook.XmlMaps("cds_Map") ActiveWorkbook.XmlImport URL:=

"C:\projects\Excel\cds02.xml", ImportMap:= map, Overwrite:=False End Sub

Run the AppendXMLData() method on the same worksheet that you imported the original XML discography information on. The data should look like that shown in Figure 3-13.

Figure 3-13. XML data appended

Using the XmlMaps collection's Import method, the same call might look like this:

Sub AppendXMLIntoExistingMap() Dim sNewData As String sNewData = "C:\projects\Excel\cds02.xml"

ActiveWorkbook.XmlMaps("cds_Map").Import sNewData, False End Sub

The Overwrite argument is set to False, causing the data to be appended to the end of your data range. Set it to True to write over the data.

Sub ImportXMLIntoExistingMap() Dim sNewData As String sNewData = "C:\projects\Excel\cds.xml"

ActiveWorkbook.XmlMaps("cds_Map").Import sNewData, True End Sub

0 0

Post a comment