8.4. The Excel Object Model The Excel Object Model exists to allow programmers to write code to perform Excel-related functions. The Object Model includes classes for such things as worksheets, cells, cell ranges, charts, and many other Excel constructs. These classes allow you to write code that inserts, modifies, formats or deletes data in the cells, creates new worksheets, adds charts, and performs almost any other function that can be performed by an Excel user. In Office 2003 the Excel Object Model was extended to support XML. It now has classes for things like the XmlMap object. Complete coverage of the Excel Object Model is outside the scope of this book, but this section will provide some pointers on understanding the XML-specific aspects of it. 8.4.1 XML-related concepts Unlike the Word or InfoPath object models, which represent the underlying document as XML, the Excel object model does not treat the underlying worksheet as an XML document. Instead, it keeps track, by means of XPath expressions, of the relationship between cells and their locations in the mapped schema. When the map is imported or exported, Excel performs the appropriate transformation based on the XPath expressions. Two terms are important to understanding the XML Object Model: maps and lists. Maps were introduced in Chapter 7, "Using XML data in spreadsheets", on page 132. A map is the relationship between an Excel workbook and a schema. Our stock quote example has a single map, which ties stock quotes.xls to the schema of the documents returned by the stock quote Web service. It is possible for a single workbook to have multiple maps, with different schema definitions. It is also possible for a single map to have multiple schemas associated with it, if the "main" schema document includes others. Lists represent the repeating element types of the map. For example, our stock quote example has one list, which represents the repeating set of stock quotes. This list has multiple columns associated with it. 8.4.2 Maps Maps in the object model are represented by the XmlMap class. You can retrieve the maps associated with a workbook through the XmlMaps collection. For example, the following code will return the first map of the active workbook: Example 8-6. Return the first map Dim firstMap As XmlMap Set firstMap = ActiveWorkbook.XmlMaps(1)
You can then use the XmlMap object to import or export the XML data. For example, you can export the data to the XML file C:\expenses.xml with: Example 8-7. Export to XML file firstMap.Export("C:\expenses.xml")
You can also use the ExportXML method to export the XML data as a string. For example, you can assign a string containing the XML data to the variable xmlString with: Example 8-8. Assign XML data string to variable Dim xmlString as String firstMap.ExportXML(xmlString)
To import, you can use the Import method. For example, to import the XML data from the file C:\expenses.xml: Example 8-9. Import XML data from a file firstMap.Import("C:\expenses.xml")
In addition to these methods, the XmlMap class has a number of properties that allow you to set various options on the map; for example: AdjustColumnWidth indicates whether to adjust column widths to fit the data when importing. AppendOnImport specifies whether to append to the existing data in the map or overwrite it. 8.4.3 Lists, cells and XPath expressions Lists, which represent repeating XML data, are represented by the ListObject class. For example, our stock quotes example has one list that represents all the repeating stock quote information. The relationship between lists, columns and their maps and XPath expressions is depicted in Figure 8-7. Figure 8-7. Structure of list-related Object Model components 
You can retrieve the lists associated with a worksheet through the ListObjects collection. A ListObject may be associated with one or more ListColumn objects. In our stock quote example, the ListObject is associated with eight ListColumn objects, one for each column of data. Each list column has an XPath expression associated with it. In our stock quotes example, the first column in the list is associated with the XPath expression: Example 8-10. XPath expression ArrayOfHistoricalQuote/HistoricalQuote/Date
Individual cells can have XPath expressions associated with them as well. In our expense report example in Chapter 7, "Using XML data in spreadsheets", on page 132, there were several cells that contained non-repeating data, such as the employee name and the charge code. These cells have their own XPath expressions associated with them to locate their data. For example, the following code will display a dialog containing the XPath expression associated with cell E5: Example 8-11. Display associated with XPath expression Set myCell = Range("E5") MsgBox (myCell.XPath)
It is also possible to determine which range of cells is mapped to the data located by a given XPath. This is accomplished using the XmlDataQuery method of the Worksheet class. For example, the following code will return a range representing the first column of the stock quotes worksheet. Example 8-12. Return cells mapped to located data Set myRange=myWorkSheet.XmlDataQuery( "ArrayOfHistoricalQuote/HistoricalQuote/Date")
This section has given you a taste of the capabilities of the Excel Object Model. Basically, any XML-related task that can be performed manually in Excel can also be performed using the object model, and then some. This includes creating, modifying and deleting maps, setting all the XML-related options, and performing imports and exports. The Excel Object Model can be viewed and navigated using Object Browser in the Visual Basic Editor. | 
