The Excel Object Model

The Excel object model is a collection of objects that let you work with Excel components in VBA code. You can get information about the Excel object model through the object model diagram in Help or the Object Browser in the Visual Basic window.

Viewing the Excel Object Model

As with Word, it is easy to find a diagram of the Excel object model in Help. First, open the Visual Basic window from an Excel worksheet. Type “object model” into the Answer box on the toolbar, and then select Microsoft Excel Objects (or Microsoft Excel Object Model, for Excel 2003) to open the corresponding Help topic. A portion of the Excel object model diagram in this Help topic is shown in Figure 13.3.

Figure 13.3

Directly under the top-level Application object, you can see Workbooks and Worksheets and Charts, representing the familiar Excel worksheets and charts you work with in the interface. In addition to these objects, when you work with Excel using Automation code you will also need to work with the Range object, which represents a set of cells designated by row and column references. You can see the Range object in the secondary diagram opened by clicking on the red triangle next to the Worksheets collection, as shown in Figure 13.4.

Figure 13.4

Along with the diagrams in Help, there is another way to view the Excel object model, which may be more helpful when you are writing VBA code: This is the Object Browser, which is opened from the Visual Basic window of any Office component, using the F2 function key. This dialog lets you select objects and their properties, methods, and events, and open Help topics as needed. To view Excel objects, select Excel in the unlabeled Libraries selector at the upper left of the window. The Classes list contains objects, collections, and enumerations (abbreviated enums)—collections of named constants that can be used as argument values. When you make a selection in the Classes list, all the attributes of the selected object, collection, or enum are displayed in the Members list.

Figure 13.5 shows the properties and methods of the Worksheets collection.

Figure 13.5

On selecting a property, method, or event of an entry in the Classes list, you will see its syntax displayed in the Object Browser’s status bar. Figure 13.6 shows the syntax for the PrintOut method of the Worksheets collection. The method’s argument names (From, To, and so forth) are italicized and bracketed. Unlike Word, the data type of the arguments is not given, although the enum used to select appropriate values is listed, where relevant. For enum settings, you can click the enum name to open it and see the available selections. (See Chapter 11, Working with Word, for more information on using enums.)

Figure 13.6

The Basic Automation Commands

To initiate an Automation session, the first step is to create an Automation object representing the Office component, in this case, Excel. This is generally done by setting an object variable to the top-level Application object in the Excel object model, using either the CreateObject or GetObject function. CreateObject creates a new instance of Excel, while GetObject uses an existing instance of Excel. To avoid creating extra Excel instances, I like to use the GetObject function initially, with a fallback to CreateObject in a procedure’s error handler, so that an existing instance of Excel will be used, if there is one, and a new instance will only be created if it is needed.

With Excel (unlike Word and Outlook), it can be useful to just set a reference to a Workbook or Worksheet object, but you will often need to use methods or properties of the Excel Application object, so in that case it is best to create a Excel application object that can then be used for all Automation work with Excel. The syntax for setting a reference to an existing instance of Excel, using the GetObject function, follows:

 Set gappExcel = GetObject(, “Excel.Application”)

If this line of code fails (because Excel is not running), error 429 occurs, and the following error handler then runs a line of code using the CreateObject function to create a new instance of Excel and resumes running the code after the GetObject line.

 ErrorHandler: If Err.Number = 429 Then    ‘Excel is not running; open Excel with CreateObject    Set gappExcel = CreateObject("Excel.Application")    Resume Next Else    MsgBox "Error No: " & Err.Number & "; Description: "    Resume ErrorHandlerExit End If 

Using the gappExcel variable, you can access any of the objects in the Excel object model, to create workbooks, worksheets, and charts, and work with them as needed. Some of the code samples in the “Exporting Data from Access to Excel” and “Importing Data from Excel” sections in this chapter (and the Excel Data Exchange sample database) use the gappExcel variable as the starting point for working with various components of the Excel object model, using Automation code.

Workbooks, Worksheets, and Ranges

Although people generally refer to .xls files as worksheets, technically they are workbooks, each containing one or more worksheets. This means that when you need to work with worksheets, you have to first set a reference to the relevant workbook, then work down to the specific worksheet you need to work with. The sample code segment that follows creates a new workbook, sets a reference to the first worksheet in the new workbook, and makes it visible.

 Set wkb = gappExcel.Workbooks.Add Set wks = wkb.Worksheets(1) wks.Activate gappExcel.Application.Visible = True 

This code is useful for creating a brand-new worksheet for accepting data from Access. If you need to work with a named worksheet, use the following syntax, which sets a reference to a workbook, then to a specific worksheet, and prints a value from one of the worksheet’s cells to the Immediate window.

    If CheckDocsDir = False Then       GoTo ErrorHandlerExit    End If strWorkbook = GetDocsDir & "Categories.xls" Set wkb = GetObject(strWorkbook) Set wks = wkb.Sheets("Northwind Food Categories") Debug.Print wks.Range("B3").Value 

Unlike Word and Outlook, where you are usually creating separate objects (Word documents and Outlook items) from records in an Access table, when exporting Access data to Excel you are creating a worksheet with rows in the worksheet corresponding to records in an Access table or query. Because of this, it generally isn’t necessary to screen data for missing values in fields. If you are creating Outlook mail messages, you need to check that the Access record has an email address, and if you are creating Word letters, you need to check that the Access record has a complete mailing address—but it won’t cause problems if a cell in an Excel worksheet is missing an email address value or a street address. Because of this, when exporting data to Excel or importing data from Excel I generally prefer to export or import the entire table, query, or datasheet, and do whatever filtering or data type conversion is needed in the target application. However, there are times when you need to work with Excel worksheets using Automation code, primarily when you want to format a worksheet (data exported to worksheets).

A Shortcut: Using the Excel Macro Recorder

As with Word (but unlike Outlook), Excel has a macro recorder that you can use to capture the VBA syntax corresponding to various actions in the Excel interface. To use this handy feature, turn on the macro recorder by selecting Tools|Macro|Record New Macro, and click OK (there is no REC button on the Excel status bar). Go through the steps you want to perform, and stop the recorder by clicking the Stop Recording button on the small Macro Recorder toolbar. The resulting saved macro (by default called Macron) contains the VBA code to do the steps you recorded. This code is likely to be verbose, and in need of trimming (for example, you rarely need to assign a value to every single argument of a method), but it is very helpful as a preliminary step to writing Automation code.

The following macro was recorded from these actions: Select Column I in a worksheet and sort by it.

 Columns("I:I").Select Range("A1:K92").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=     xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,     DataOption1:=xlSortNormal 

To modify this code for use in Automation code, all you need to do is insert an Excel Application variable in front of each line.

 gappExcel.Columns("I:I").Select gappExcel.Range("A1:K92").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=     xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,     DataOption1:=xlSortNormal