Beginning Access™ 2007 VBA

11.2. Automation

Automation refers to the capability to control one program from within another. For example, you can control Microsoft Excel from within your Access applications. In such a case, Access would be the automation client and Excel the automation server.

In order to use automation, you must first add a reference to the object library for the application that you want to automate. As you learned before, you can add references using Tools References from the Visual Basic Editor.

Go ahead and add a reference to Excel, Word, and Outlook, as shown in Figure 11-5. You will be looking at examples of each of these next.

Figure 11.5. Figure 11-5

11.2.1. Working with Automation Objects

Before you can automate external applications, you need to declare an object variable. The following example shows how you declare an object variable for Excel:

Dim objExcel As New Excel.Application

The preceding line of code declares a new object and creates a new instance of Excel in a single line of code. If you want to control the point when a new instance of Excel is created, you can separate the declaration and instantiation into two lines of code, as shown next.

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application

After you have created an instance of the automation object, ...