Using Automation to Connect Applications

Automation is a Microsoft technology that allows one application to access the commands and features of another application remotely. In plain English, this means that you can use an Excel function from within a Microsoft Word macro without spending the time to write the function yourself or knowing exactly how it goes about its business. Automation lets you expand your existing macros by integrating the best features of other applications without the development costs— you simply access the application objects using the necessary arguments, and let Automation do the rest!

Not all Windows-based applications support Automation, only those that have been specifically designed to make available, or expose, their functionality via an object model that uses recognizable properties, methods, and events. The Windows-based applications that do expose their objects are called object or server applications, and the programs that can construct macros using these objects are called controlling or client applications. (A Visual Basic Editor is required to be a client application.) Table 41-1 shows the complete list of Microsoft Office applications and their ability to act as server and client applications.

Table 41-1. Office 2000 Applications Support Automation as Servers and Clients

Office 2000 Application	Exposes Objects (Server)	Has VB Editor (Client)
Microsoft Word	X	X
Microsoft Excel	X	X
Microsoft PowerPoint	X	X
Microsoft Access	X	X
Microsoft Outlook	X
Microsoft FrontPage	X
Microsoft Publisher
Microsoft Internet Explorer	X

NOTE

---

Microsoft is currently licensing the Visual Basic for Applications programming language, so you'll soon find other applications for Windows that support object Automation and Visual Basic macros.

Automating Application Objects Step by Step

Creating an Office macro that uses the features of an object application is a straightforward process. First you need to add a reference to the object library that you want to use in your macro and determine how the commands you want to run translate into the server application's object model. Then you need to complete a few simple programming steps, which include declaring an object variable, using the object's methods and properties, and (if necessary) releasing the object's memory space when you're finished. The following list details the steps you need to follow.

Step 1 Add a reference to the object library that you want to use in your macro module by using the References command on the Tools menu. After you activate the object library, you can browse the objects, properties, and methods it exposes by using a tool called the Visual Basic Object Browser.

NOTE

---

The object library for each application that supports Automation is installed automatically when you run the application's setup program. For most Office applications, this library is Microsoft Office 9 Object Library. If you plan to distribute your macro, be sure that each computer in your workgroup has a copy of this library and the Office application software.

Step 2 Write your macro using the Office application you want. In the procedure in which you plan to use Automation, create an object variable by using the Dim statement, and then load an Automation object into the object variable by using the CreateObject function. This example creates a PowerPoint object variable:

 Dim ppt As Object       ''use ppt as variable name  Set ppt = CreateObject("Powerpoint.Application") 

Step 3 Use the methods and properties of the Automation object in the procedure, consulting the Help files in the Object Browser or the object application documentation for the proper syntax. These PowerPoint statements display the PowerPoint user interface, open a presentation, and run the slide show:

 ppt.Visible = True      'open and run presentation  ppt.Presentations.Open "c:\pptfacts.ppt"  ppt.ActivePresentation.SlideShowSettings.Run 

Step 4 When you have finished using the object application, release the object variable to conserve memory:

 Set ppt = Nothing       'release object variable 

In the next section, you'll create a Word macro that uses the Excel PMT (payment) function to calculate loan payments. The macro will be built entirely in Word, using the necessary Excel commands long distance, by means of Automation.