Adding XML to a Worksheet Programmatically
|
| < Day Day Up > |
|
As you might expect, every XML-related action you can take using the Excel interface has its counterpart in the Excel object, although there are times when you have to dig a bit to find out how to do something in VBA that takes a simple and intuitive action when going through the interface. One example of that phenomenon is the series of actions you need to take to create a single data list (rather than a series of lists) from an XML schema.
Mapping a Schema to a Worksheet Programmatically
When you map an XML schema to a worksheet in VBA, you do so by creating a variable that contains a reference to an XmlMap object, which is the object used to represent a schema contained in an .xsd file. Table 26-3 lists the XmlMap object's properties and methods.
| Property or Method | Description |
|---|---|
| Property | |
| AdjustColumnWidth | A Boolean value that, when set to True (the default), causes Excel to change the column width to fit the data imported into that column. Setting the property to False causes the columns to retain their width at the time of the import. |
| AppendOnImport | A Boolean value that, when set to False (the default), causes data imported into a schema to overwrite the existing values. Setting this property to True causes newly imported data to be appended to an existing list. |
| IsExportable | A Boolean value that returns True if Excel can use the XPath objects in the specified schema map to export XML data and if all XML lists mapped to the specified schema map can be exported. |
| Name | A string that contains the name of an XML map. The string must be unique within the workbook and cannot exceed 255 characters. |
| PreserveColumnFilter | A Boolean value that, when set to True (the default), causes any list filter to be retained when the map is refreshed. |
| Property | |
| PreserveNumberFormatting | A Boolean value that, when set to True, retains any number formatting changes when the XML map is refreshed. The default value is False. |
| RootElementName | A read-only string that contains the name of the root element of the mapped schema. |
| SaveDataSourceDefinition | A Boolean value that, when set to True (the default value), causes Excel to save the data source definition of the mapped schema with the workbook. |
| Schemas | A property that returns an XmlSchemas collection that contains each schema mapped to the active workbook. |
| ShowImportExportValidationErrors | A Boolean value that, when set to True, causes Excel to display a dialog box that details any schema-validation errors that occur when data is imported or exported through the specified XML schema. The default value is False. |
| Method | |
| Delete | The Delete method removes the named schema from a workbook. |
| Export(Url, Overwrite) | The Export method writes the contents of cells mapped to the specified XmlMap object to an XML data file. The Url argument is a string that contains the full path of the file to which you want to write the XML data. The Overwrite argument, when set to True, replaces any existing file with the same name. (The default value is False.) The Export method returns one of two constants: xlXmlExportSuccess, which indicates the export happened without error, or xlXmlExportValidationFailed, which indicates the data did not match the XmlMap schema. |
| ExportXml(Data) | The ExportXml method writes the contents of cells mapped to the specified XmlMap object to the string named in the Data argument. The method returns one of two constants: xlXmlExportSuccess, which indicates the export happened without error, or xlXmlExportValidationFailed, which indicates the data did not match the XmlMap schema. |
| Method | |
| Import(Url, Overwrite) | The Import method writes data from the file at the specified URL to cells mapped with the XMLMap that called the method. The Url argument holds a file path or Web address. The Overwrite argument is a variant that, when set to False (the default), appends the imported data after any existing data, or when set to True overwrites any existing data. The Import method returns one of three constants: xlXmlImportElementsTruncated, which indicates that the contents of the specified XML data file have been truncated because the XML data file is too large for a cell; xlXmlImportSuccess, which indicates the import was successful; and xlXmlImportValidationFailed, which indicates that the contents of the XML data file don't match the specified schema. |
| ImportXml(XmlData, | The ImportXml method writes data from a string variable (specified in the XmlData argument) to cells mapped with the XmlMap that called the method. The Overwrite argument is a variant that, when set to False (the default), appends the imported data after any existing data, or when set to True overwrites any existing data. The method returns one of three constants: xlXmlImportElementsTruncated, which indicates that the contents of the specified XML data file have been truncated because the XML data file is too large for a cell; xlXmlImportSuccess, which indicates the import was successful; and xlXmlImportValidationFailed, which indicates that the contents of the XML data file don't match the specified schema. |
All the schemas assigned to a workbook are stored in the Workbook object's XmlMaps collection. You can refer to individual members of the collection by noting their names, as in the following statement:
ActiveWorkbook.XmlMaps("Root_Map") Although most of the properties and methods you'll need to work with XML maps are contained in the XmlMap object, the XmlMaps collection does contain the vital Add method, which you use to assign a schema to a workbook. The Add method has this syntax:
Add(Schema, RootElementName)
The Schema argument is a string variable that contains the full path of the schema to add to the workbook, and the RootElementName argument contains the name of the root element in the schema. The following procedure adds the MySuppliers.xsd schema to the active workbook:
Sub ApplySchema()
Dim myMap As XmlMap
Dim xSchemaFile As String
xSchemaFile = "C:\MySuppliers.xsd"
Set myMap = ActiveWorkbook.XmlMaps.Add(xSchemaFile, "Root")
End Sub
This procedure defines an object variable (myMap) to hold the reference to the XmlMap. After you assign the schema file path to the xSchemaFile variable, you use the Set command to assign the named schema file to the active workbook's XmlMaps collection.
When you attach a map to a workbook, the map is assigned the name of the root element followed by an underscore and the word Map. The map you just added, which has a root element named Root, would be assigned the name Root_Map.
To delete the XmlMap you just added to your workbook, you would run the following procedure:
Sub RemoveMap()
ActiveWorkbook.XmlMaps("Root_Map").Delete
End Sub
| Note | If a map has been attached to a workbook and you attempt to assign the map to the workbook again, Excel will add a number to the end of the map name. For example, adding the Root_Map map to a workbook additional times would result in the map being named Root_Map2, Root_Map3, and so on. |
If you wanted to write the data currently within the cells assigned to the Root_Map schema to a file named SuppliersBackup.xml, you could use the following procedure:
Sub BackupXML()
ActiveWorkbook.XmlMaps("Root_Map").Export _
Url:= "C:\SuppliersBackup.xml", Overwrite:=True
If Err.Number = 0 Then
MsgBox "Data exported to SuppliersBackup.xml successfully."
Else
MsgBox "There was a problem exporting to SuppliersBackup.xml."
Exit Sub
End If
End Sub
| Warning | Because the Overwrite argument is set to True, this procedure will delete any existing data in the SuppliersBackup.xml file. Also, if the data list is empty, the procedure will generate an error. |
Mapping Schema Elements to Cells Using XPath
After you have mapped a schema to a workbook, you need to map every element you want to appear in the worksheet to a range of cells so that you have a place for the associated XML data to reside. You don't need to assign every element in a schema to a cell or range; Excel will skip any unused elements when it imports the data from your XML file.
You identify which element in a schema file to map to a cell using the XML Path language (XPath). Although the full XPath specification is long and involved, it boils down to a system of positively identifying the schema element to be mapped. In fact, XPath notation is very similar to the notation you use to identify the path of a Microsoft Windows file. For example, the path of a file might be C:\ExcelProg\MySuppliers.xml, which indicates that the MySuppliers.xml file is in the ExcelProg directory on the C drive. The difference between the XPath language and the Windows file path notation system is that you need to specify the location of an element with a schema. As an example, consider the schema displayed in Figure 26-4 on page 547. The root element is named Root, which has the subelement Supplier, which in turn has the subelement SupplierID. The XPath notation for the SupplierID subelement is as follows:
/Root/Supplier/SupplierID
The Excel object model contains an XPath object with the properties and methods that Excel needs to use XPath data in its operations. Table 26-4 lists the XPath object's properties and methods.
| Name | Description |
|---|---|
| Property | |
| Map | A read-only property that returns the XmlMap object that represents the schema assigned to the XPath object. |
| Repeating | A read-only Boolean value that returns True if the XPath object is assigned to a list, or returns False if the object is assigned to a single cell. |
| Value | Returns a string that represents the XPath for the object. |
| Method | |
| Clear | Clears the schema mapping from the cell or cells mapped to the specified XPath. |
| SetValue(Map, XPath, SelectionNamespace, Repeating) | The Map argument (required) is an XmlMap variable representing the schema map into which you'll import your XML data; XPath (required) is a valid XPath statement; SelectionNamespace (an optional variant) specifies any namespace prefixes (you can leave this argument out if you put the full XPath in the XPath argument); Repeating (an optional Boolean) indicates whether the XPath object should be mapped to a single cell (False) or to a column in the list (True). |
For more (and very technical) information on the XML Path language, visit the official XPath Web site at http://www.w3.org/TR/xpath.
The XPath object's SetValue method is the most important method in the bunch-it lets you map a schema element directly to a cell or range. The following procedure defines the data file that contains the XML data, assigns the schema to be used in the SetValue method calls to the myMap object variable, and maps the elements to specific ranges. The result of the procedure is shown in Figure 26-7.
Figure 26-7: The AssignElementsToRanges procedure maps schema elements to cell ranges on your worksheet, creating lists.
| Warning | This procedure will only run correctly if you have previously mapped the MySuppliers.xsd schema to the active workbook. You can do so by running the ApplySchema procedure from the 'Mapping a Schema to a Worksheet Programmatically' section found earlier in this chapter or by mapping the schema to your workbook manually.
Sub AssignElementsToRanges() |
The AssignElementsToRanges procedure creates a list for each element in the MySuppliers.xsd schema, but creating individual lists poses a problem: when you type a new data element into a list (for example, the SupplierID list) and press Tab, you're taken to a new row in the same list, not to what is logically the next cell in the list. You can tell that the procedure created a series of lists, not a single list, by the presence of blue lines on the borders of the columns. If you were to map the same schema to your worksheet by dragging the Supplier element from the XML Source task pane to cell B2, you would create a single list. The good news, however, is that if you filter a list by clicking the down arrow at the right edge of a column heading and selecting a filter criteria, you still filter all the lists. But how do you create a list that lets you tab from one column to another?
 |
Mapping a Schema to a Single List
When you create a multi-column list, you create a seamless object into which you can type data. But creating a series of lists, as in this chapter's AssignElementsToRanges procedure, doesn't let you use the Tab key to move from one column to the next in the collection of lists. So how do you map a series of schema elements in such a way that you get full list functionality? By creating a list on your worksheet and mapping the schema elements to the list's column headers. The following procedure creates just such a list for the MySuppliers.xsd and MySuppliers.xml files, with the results shown in Figure 26-8.
Figure 26-8: Assigning schema elements to column headers in an existing data list gives you full list functionality and a data map.
| Caution | This procedure assumes the worksheet that you're manipulating is blank and that you haven't mapped the MySuppliers.xsd schema to the active workbook. In fact, it's best if you run this procedure on a new workbook.
Sub CreateOneList() |
|
In this chapter, you've learned how to use the new XML capabilities in Excel 2003. Although the ability to save files as XML workbooks has been around since Excel Version 2002, you now have greater access to the XML object model and can import, export, and manipulate data within your workbooks programmatically. The tools at your disposal make it possible to transport data across a wide variety of platforms, making it easier for you to work with customers, suppliers, and colleagues regardless of the spreadsheet application they use.
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161