Writing Automation Code

If you are going to work with Excel worksheets in code—as opposed to simply exporting data from a table or query to a worksheet—you need to set a reference to the Excel object model in your database, so that you can work with Excel objects using Automation code. (See Chapter 11, Working with Word, or an explanation of using Automation code to communicate with another Office application.) To set a reference to the Excel object model, open the database’s Visual Basic window, drop down the Tools menu, and select References. Locate the Microsoft Excel Object Library and check it, as shown in Figure 13.2. The version will vary according to the version of Office you are running: 9.0 for Office 2000, 10.0 for Office XP, and 11.0 for Office 2003.

Figure 13.2

Unlike Word and Outlook, Excel references are generally upgraded and downgraded appropriately if an Access 2000 database is opened alternately in Access 2000 and higher versions, so you can set the reference to the version you are currently using. The version number should change as needed, depending on the version of Office you are using.

The “Some Handy Toolbar Buttons” section in Chapter 11 describes how to put buttons on your toolbar to quickly open the Visual Basic window and the References dialog, which can save time when working with references and VBA code in general.