When you open an XML file, either programmatically or through the application interface, Excel automatically creates an XML map. An XML map is represented in VBA by the XmlMap object. An XML map serves to map the elements and attributes of an XML file to worksheet ranges. For example, the XML map named word_find_Map in Figure 8.7 maps the range A1:A23 to the <topic> element in the words.xml document and the range B1:B23 to the <word> element.
Each XmlMap object is contained in an XmlMaps collection object which is returned from the Workbook object via the XmlMaps property. The following code loops through the XmlMaps collection in the active workbook and prints the names of all XmlMap objects in the active workbook to the Immediate window.
Dim maps As XmlMaps
Dim myMap As xmlMap
Set maps = ActiveWorkbook.XmlMaps
For Each myMap In maps
The XmlMap object includes four methods for importing and exporting data between an XML file or string variable, and worksheet ranges mapped to the object. Use the Import() and Export() methods of the XmlMap object to import and export data between an XML file and mapped ranges on a worksheet. The following example first imports data from the XML file called words.xml using an existing XmlMap object in the active workbook and then exports the same data to the file words2.xml. The file words2.xml is created if it doesn't already exist.
Dim filePath As String, filePath2 As String filePath = ActiveWorkbook.Path & "\words.xml" filePath2 = ActiveWorkbook.Path & "\words2.xml" ActiveWorkbook.XmlMaps(1).Import URL:=filePath, Overwrite:=True ActiveWorkbook.XmlMaps(1).Export URL:=filePath2, Overwrite:=True
The URL argument of the Import() and Export() methods is a string that specifies a file's path. When the Overwrite argument is true, the data is overwritten in the worksheet cells or the file, depending if you are importing or exporting data, respectively. At least one XmlMap object (note the index value used with the XmlMaps property) must already exist in the active workbook, or the previous code listing will fail to execute. Furthermore, the XmlMap object should be compatible with the structure of the XML file words.xml, or the data will not be properly mapped to the appropriate ranges in the worksheet. Presumably, you can create the XmlMap object from a compatible file by opening it in the Excel application prior to invoking these methods, so this shouldn't present a problem.
To copy data between a string variable and a mapped range on a worksheet, use the ImportXml() and ExportXml() methods of the XmlMap object. The following example exports data mapped with the XmlMap object named word_find_Map to the string variable xmlStr. The ExportXml() method returns an XlXmlExportResult constant (xlXmlExportSuccess or xlXmlExportValidationFailed) indicating the result of the data export. The names of the constants are self-explanatory.
Dim xmlStr As String
If ActiveWorkbook.XmlMaps("word_find_Map").ExportXml(Data:=xmlStr) <> xlXmlExportSuccess Then
MsgBox "Export failed"
Similarly, to copy data from the string variable xmlStr to the cells mapped by the XmlMap object named word_find_Map, I use the ImportXml() method of the XmlMap object. The content of the variable xmlStr must be structured as a well-formed XML document.
If ActiveWorkbook.XmlMaps("word_find_Map").ImportXml(xmlData:=xmlStr) <> xlXmllmportSuccess Then
MsgBox "Import failed" End If
The ImportXML() method returns an XlXmlImportResult constant that I have used to test for a successful import (the remaining two constants are xlXmlImportElementsTruncated and xlXmlImportValidationFailed).
There are several properties associated with the XmlMap object. Most notable are the Name, DataBinding, IsExportable, RootElementName, and Schemas properties. The DataBinding property returns an XmlDataBinding object. The XmlDataBinding object represents the connection between the data source (XML file) and the XmlMap object. The Refresh() method of the XmlDataBinding object quickly refreshes the mapped cells with the data from the XML file.
The IsExportable property of the XmlMap object returns a Boolean value indicating whether or not Excel can export the mapped data. Potential reasons that an export would fail include: file path error, improper mappings, or incompatibilities with the schema.
The Schemas property returns an XMLSchemas collection object contained by an XmlMap object. Typically, there is only one XmlSchema object per XmlMap object; so specifying an index value of 1 with the Schemas property returns the desired XmlSchema object. The XmlSchema object represents the schema that defines the mapped XML document.
The following code listing first exports mapped data to a file called words3.xml before out-putting the value of a few properties of an XmlMap object to the Immediate window. The XmlMap object was created from the words.xml file whose structure was listed earlier in this chapter.
Dim myMap As XmlMap Dim filePath As String filePath = ActiveWorkbook.Path & "\ words3.xml" Set myMap = ActiveWorkbook.XmlMaps("word_find_Map") With myMap
If .IsExportable Then
.Export URL:=filePath, Overwrite:=True
MsgBox "Not exportable" End If
Debug.Print .Name Debug.Print .RootElementName Debug.Print .Schemas(1).XML End With
The XML property of the XmlSchema object (returned by the Schemas property of the XmlMap object) returns a string representing the schema used in the mapping; thus, it is an excellent method for collecting a schema for an existing XML file. Unfortunately, the XML property returns the string without white space so you have to add the line feeds and indentation to make the text well-formed.
Other methods of the Workbook object you can use to save or import XML data include: SaveAsXmlData(), XmlImport(), and XmlImportXml(). The SaveAsXmlData() method exports mapped data to an XML document file. It requires two arguments—Filename and Map—that are used to specify a name for the XML file and the XmlMap object representing the mapped data.
Dim myMap As XmlMap
Dim filePath As String
Set myMap = ActiveWorkbook.XmlMaps(l)
filePath = ActiveWorkbook.Path & "\test.xml"
ActiveWorkbook.SaveAsXMLData Filename:=filePath, Map:=myMap
The XmlImport() and XmlImportXml() methods import data from an XML file and data stream (string variable), respectively. Both methods require a data source (XML file or string variable) and an XmlMap object. The arguments Overwrite and Destination are optional, but Destination must be omitted if the XmlMap object has already been loaded into the workbook. This makes sense because once an XmlMap object has been created, the data is mapped to specific ranges in the worksheet and cannot be changed. The following code imports XML data from the file sample.xml to a mapped range on the active worksheet using an existing XmlMap object (sample_Map).
Dim myMap As XmlMap Dim filePath As String filePath = ActiveWorkbook.Path & "\sample.xml" Set myMap = ActiveWorkbook.XmlMaps("sample_Map")
ActiveWorkbook.XmlImport URL:=filePath, ImportMap:=myMap, Overwrite:=True
The XmlImport() method imports data from an XML file whereas the XmlImportXml() method imports XML data from a string variable. The data stored in the string variable (xmlStr in the following example) must be that of a well-formed XML document and is assigned to the Data argument of the XmlImportXml() method.
ActiveWorkbook.XmlImportXml Data:=xmlStr, ImportMap:=myMap2, Overwrite:=True
Was this article helpful?