Chapter 7

Communicating Across Microsoft Office

Chapter Objectives

Estimated time: 45 minutes

These days many computing situations require the interaction of two or more Office applications. Such interactions often combine different forms of the same material (such as text, charts, tables, and slides). A company's sales figures, for example, may be stored in a Microsoft Access database, presented in a report written in Microsoft Word, analyzed with a Microsoft Excel worksheet, and summarized in a Microsoft PowerPoint presentation. To be able to use the same content in different Office applications, you need to establish communication among them.

In Visual Basic for Applications, you make a connection between two applications either by using the Visual Basic functions CreateObject or GetObject or by using the keyword New in a declaration statement. Either of the two functions or the keyword New establishes a link to another Office application, giving you the power to use content from one application inside another.

Quick Guide: CreateObject, GetObject, and the New Keyword

This chapter describes the use of the CreateObject and GetObject functions and the New keyword. They are built into the Visual Basic for Applications language to start an instance of an Office application. Once you start an application, you use content from one application to create a document in another. This chapter shows how to access a running instance of Excel in order to retrieve data from a worksheet. This chapter also uses PowerPoint to create a presentation from a Word document. Chapter 8 uses data from an Access database to create documents and content in Word, Excel, and PowerPoint, as well as an e-mail message in Outlook. The following table lists the tasks you'll commonly use to communicate among applications.

Task Description
Create a new instance of an object Return a reference to the object with the CreateObject function, and then assign the reference to a variable with the Set statement. For example: 

Set appXl = CreateObject _ 
    ("Excel.Application")

The underscore indicates that the line of code continues on the next line.
Declare a variable as a generic object type Type the variable name between Dim and As Object. For example: 

Dim MyObject As Object
Reference application object libraries In the Visual Basic Editor, click References on the Tools menu, select the object libraries for the applications your program will work with, and click OK.
Declare a variable as a specific object type With the appropriate object library selected in the References dialog box, type Dim, followed by the variable name, As, and the object type. For example: 

Dim appPPT As PowerPoint.Application

In this example, the Microsoft PowerPoint 9.0 Object Library must be referenced.
Declare an object variable and assign an instance of an object to it in one step Type Dim, followed by the variable name, As New, and the application object name. For example: 

Dim appPPT As New PowerPoint.Application
Assign an instance of an object that is already loaded to an object variable Return a reference to the object with the GetObject function and Class argument, and then assign the reference to the variable with the Set statement. For example: 

Set appWd = GetObject(, "Word.Application")
Start an application, open an existing file, and assign it to an object variable in one step Return a reference to the object with the GetObject function and pathname argument, and then assign the reference to the variable with the Set statement. For example: 

Set wbBook1 = GetObject("Book1.xls")
Disconnect a variable from an object Assign the value Nothing to the variable. For example: 

Set appPPT = Nothing