USING AUTOMATION
Before using Automation, you need to set up references to the various applications' object libraries, also known as TypeLibs. By setting up references, you can strongly type variables (so that you can use data types such as Sheet and Chart instead of Object), get early binding, and use constants defined in the TypeLib. (Declaring Automation variables is described in more detail in the next section, “Declaring Object Variables in VBA.”)
Suppose that you want to set a reference to the Microsoft Word 9.0 TypeLib. In the database where you want to set up a reference, open the module editor with any module, and then follow these steps:
1. | |
2. |
Select Microsoft Word 9.0 Object Library from the Available References list (see Figure 13.2). Figure 13.2. References are specific to individual databases and must be set up for each .mdb file.
|
3. |
Click OK. |
Note
If you choose not to set references in your database (or if you forget to), you can still use Automation to access the server application. Use the Object type when declaring variables to work with objects located in the server application.
Tip
If you don't have an object library that a reference is set to in a database, you will get a compile error with that database. One of first things I do when I get a compile error for no apparent reason is open a module and choose References from the Tools menu. I look for a reference with the word MISSING beside it. If I know it's a reference that isn't there, I unmark the entry. This means, of course, I can't run that particular routine, but my database will then compile. The Missing Reference error will occur if Microsoft Project is not installed on your system and you open the Chap13.mdb, and your database won't compile.
After the reference is created to the specific object library, you also can pull up the library and its objects in the Object Browser, located in the VBA modules. The Object Browser enables you to examine the object models for any applications you've referenced. For more information about the Object Browser, see Chapter 2, “Coding in Access 2000 with VBA.”
To bring up the Object Browser, open any module and then press F2. Figure 13.3 shows the Object Browser available in Access.
Figure 13.3. The Object Browser is available in applications that use VBA.
Note
Setting up references also works for ActiveX controls. After they're set up, you can bring up their objects in the Object Browser.
After setting up the references you need, you're ready to start coding for Automation. To do this, you must first declare any object variables needed.
Declaring Object Variables in VBA
Declaring object variables in VBA is the same as declaring other variables used in VBA—by using the Dim statement. For more information about declaring variables using VBA, see Chapter 2.
The following is the declaration statement for a variable used as a Word document:
Dim docWord as Word.Document
You use this variable type for objects in referenced servers. When the Excel object library is referenced, you can declare a Worksheet variable as such:
Dim shtCustomers as Excel.Worksheet
If you declare a variable of an Access object, such as a database, from another application as the client, you can specify a Data Access Object (DAO) variable type:
Dim dbOLEDemo as DAO.Database
Note
When automating Access from another application—that is, when you're using Access as the Automation server—don't forget that you need to set references to the Access objects. To do so, enter the VBA module of whatever product you're using as the client. Then choose References from the Tools menu and Microsoft Access 9.0 Object Library.
For the purpose of the examples in this chapter, the following code shows the various object variables defined globally as they appear in the declarations section of the modAutomationDemos module. You can find this module in the Chap13.mdb database on the accompanying CD-ROM in the ExamplesChap13 folder.
Option Compare Database Option Explicit Public appWord as Word.Application Public appProject As MSProject.Application Public appExcel as New Excel.Application
Note
If you want the server application to stay open after the subroutine is completed, you must declare the Object variable to be Public.
After the variables are defined, have the client create an Automation instance of the variable class you need to use. To do so, you have two different functions: the CreateObject() function for those objects that are new and the GetObject() function for those objects that already exist.
Using the CreateObject() Function
The CreateObject() function takes one parameter—the class of object to create. An example of the syntax for the CreateObject() function, creating a Word.Application object, follows:
Dim appWord as Word.Application
'Create the object variable
Set appWord = CreateObject("Word.Application")
Sometimes you may want to include a version number when you're creating an object. Some classes can't have the version number included. When the version isn't included, the following can occur:
If a copy of the server application is running, regardless of the version, that copy is used. For example, if you have Word 97 running when you execute the preceding code, that version of the application is run.
If you have no version of Word running and you've installed Microsoft Word 2000 on your system more recently than Word 97, Word 2000 is run.
For example, if you're using a Microsoft Excel object, you would use the following line of code:
Set objProject = CreateObject("Excel.Application.9")
You see more examples of using the CreateObject() function later in this chapter.
Tip
Rather than use the CreateObject() function, you can use the New keyword in a declaration statement to establish the reference when the variable is first used:
Dim appExcel as New Excel.Application appExcel.Visible = True
You don't have to use the Set statement after declaring the variable and before using the variable.
Using the GetObject() Function
Use the CreateObject() function when you want to create a new instance of the server class. Use the GetObject() function to create a reference to an already existing instance. In the case of a Word document, server class is the full path and filename—for example, Set objDocument = GetObject("c: empExample.doc")
The actual syntax for the GetObject() function is
Set objVariable = GetObject(strPathName, strClass)
where
objVariable is the object variable.
strPathName is the full path and file name of the object in which you're creating a reference.
strClass is the Automation class type. This value is the same as that used for the CreateObject() variable.
Based on how you use these parameters, different results occur:
If a strPathName is given, that object is referenced.
If strPathName is set to an empty string (""), a new instance is created of strClass.
If strPathName is left out altogether with the strClass specified, a running copy of the application given in the strClass parameter starts. If no strClass application (the application represented) is running, an error is returned.
The GetObject() function also lets you specify part of a file, such as a range in a spreadsheet.
After you're through with an Automation instance, you want to set the object variable to Nothing. The syntax for this is as follows:
Set objDocument = Nothing
The best way to learn Automation is to examine code samples. To help you learn the code, I've included quite a few examples in the Chap13.mdb sample database. This database is found on the accompanying CD-ROM in the ExamplesChap13 folder. When you open Chap13.mdb, the AutomationDemoCalls form opens (see Figure 13.4).
Figure 13.4. The nine demos included in this form show how to control other Office applications from Access.
