Creating an Object Variable to Automate Another Office Application

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Working with the objects in another Office application through VBA code is very similar to using code to work with the objects within the code's host application. In most cases, you begin by creating an object variable that points to the Application object representing the Office application that contains the objects you want to work with. In general, you create an early-bound object variable by using the New keyword. However, there are limited circumstances where you may choose to use the CreateObject or GetObject function to create an object variable. For more information, see "Using the CreateObject and GetObject Functions" later in this chapter.

When you write VBA code in an application that manipulates objects within that same application, the reference to the Application object is implicit. When you are automating another application, the reference to the Application object generally must be explicit. The following two examples illustrate this difference. The first example contains VBA code intended to be run in Word. The second example contains VBA code intended to be run from another Office application (or any application that supports Automation through VBA). For the second example to work, a reference must be set to the Microsoft Word 9.0 object library in the application the code is run from.

Sub CodeRunningInsideWord()
   Dim docNew As Word.Document

   ' Add new document to Documents collection.
   Set docNew = Documents.Add
   ' Type text into document.
   Selection.TypeText "Four score and seven years ago"
   ' Display document name and count of words, and then close document without
   ' saving changes.
   With docNew
      MsgBox "'" & .Name & "' contains " & .Words.Count & " words."
      .Close wdDoNotSaveChanges
   End With
   Set docNew = Nothing
End Sub

The CodeRunningInsideWord procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

Sub CodeRunningOutsideWord()
   Dim wdApp      As Word.Application
   Dim docNew   As Word.Document
   
   ' Create new hidden instance of Word.
   Set wdApp = New Word.Application
   ' Create a new document.
   Set docNew = wdApp.Documents.Add
   ' Add text to document.
   wdApp.Selection.TypeText "Four score and seven years ago"
   ' Display document name and count of words, and then close
   ' document without saving changes.
   With docNew
      MsgBox "'" & .Name & "' contains " & .Words.Count & " words."
      .Close wdDoNotSaveChanges
   End With
   wdApp.Quit
   Set wdApp = Nothing
End Sub

The CodeRunningOutsideWord procedure is available in the modGetRecords module in AutomatingWord.xls in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

In most cases, you will create an object variable that refers to the top-level object representing the application you want to access through Automation, the Application object. Once you have the reference to the Application object, you use additional references to that object's child objects to navigate to the object or method you want to manipulate. You assign object variables to child objects by using a method of a higher-level object with the Set statement.

However, Excel and Word also allow you to create a top-level reference to certain child objects of the Application object. For this reason, it is possible to rewrite the previous CodeRunningOutsideWord procedure to start from a reference to a Word Document object, like this:

Sub CodeRunningOutsideWord2()
   Dim docNew As Word.Document
   
   Set docNew = New Word.Document
   Set docNew = Documents.Add
   ' The following line uses the Application property to access the
   ' implicit instance of the Word Application object.
   docNew.Application.Selection.TypeText "Four score and seven years ago"
   With docNew
      MsgBox "'" & .Name & "' contains " & .Words.Count & " words."
      .Close wdDoNotSaveChanges
   End With
   docNew.Application.Quit
   Set docNew = Nothing
End Sub

The CodeRunningOutsideWord2 procedure is available in the modGetRecords module in AutomatingWord.xls in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

Similarly, Excel allows you to create a top-level reference starting from the Workbook object. You can do this in either of two ways:

• By using the Excel.Sheet class name to create a workbook that contains a single worksheet.

 -or- 

• By using the Excel.Chart class name to create a workbook that contains a worksheet with an embedded Chart object and another worksheet that contains a default data set for the chart.

To create a Workbook object either way, you must use the CreateObject function, because the Excel.Sheet and Excel.Chart class names don't support the New keyword. For example, to automate Excel starting with a top-level reference to a Workbook object that contains a single worksheet, use code like this:

Dim wbkSheet As Excel.Workbook
Set wbkSheet = CreateObject("Excel.Sheet")

To automate Excel starting with a top-level reference to a Workbook object that contains a worksheet with a chart and another worksheet containing a default data set for the chart, use code like this:

Dim wbkChart As Excel.Workbook
Set wbkChart = CreateObject("Excel.Chart")

When you are automating Word starting from a Document object or automating Excel starting from a Workbook object, an implicit reference is created to the Application object. If you need to access properties and methods of the Application object, you can use the Application accessor property of the Document or Workbook objects. While using the Document or Workbook objects as top-level objects may reduce the amount of code you have to write somewhat, in most cases your code will be easier to understand and more consistent if you start from a reference to the Application object.

The following table shows all the top-level Office objects you can reference and their class names.

You can see the complete object models for each Office application in the .

Automating the Visual Basic Editor

In addition to using code to work with other Office applications, you can also use Automation code to work with the objects exposed by the Visual Basic Editor's object model. You can use the Visual Basic Editor's object model to work with the objects in its user interface, such as its windows and command bars, which allows you to develop add-ins to customize and extend the Visual Basic Editor's user interface. Additionally, you can use the Visual Basic Editor's object model to work with your VBA project itself to add and delete references, to set and read project properties, and to work with the components that make up your project, such as standard modules, class modules, and UserForms. This feature allows you to write code to maintain references, to document and set properties for projects, and to work with existing components and add new ones.

To work with the Visual Basic Editor's objects, first you must establish a reference to its type library, which is named Microsoft Visual Basic for Applications Extensibility 5.3. To write code to work with the Visual Basic Editor, you must initialize a variable to work with the Visual Basic Editor's top-level object, the VBE object. However, you can't reference the VBE object directly. This is because the Visual Basic Editor isn't an independent application or service; it's running as part of the host application's process. To initialize an object variable to work with the Visual Basic Editor, you must use the VBE accessor property of the host application's Application object. The VBE property is available in all Office applications except Outlook. The following example shows how to initialize an object variable to work with the Visual Basic Editor:

Dim objVBE As VBIDE.VBE
Set objVBE = Application.VBE

To see code samples that use the Visual Basic Editor's object model to display VBA project property and component information, see modProjectInfo in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM. For information about creating COM add-ins for the Visual Basic Editor, see Chapter 11, "Add-ins, Templates, Wizards, and Libraries." For an overview of working with the Visual Basic Editor's object model, see the Visual Basic Language Developer's Handbook by Ken Getz and Mike Gilbert (Sybex, 1999).

Note   The Access Application object provides a References collection and Reference object that allow you to work with references in an Access VBA project without requiring you to establish a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 type library. To view a code sample, see the ReferenceInfo procedure in the modReferences module in Startup.mdb in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM. For more information about the Access References collection, search the Microsoft Access Visual Basic Reference Help index for "References collection."

Using the Set Statement and the New Keyword

You start Automation code by declaring object variables with a specific object type that represents the top-level object and then declaring any child objects you want to reference. You then create an instance of the top-level object by using the Set statement and the New keyword. However, the New keyword can't be used to create a new instance of a child object. To create an instance of a child object, use the appropriate method of the parent object along with the Set statement.

In the following example, the top-level Excel Application object variable is assigned by using the Set statement and the New keyword. The object variable representing the Workbook child object is assigned by using the parent object's Add method and the Set statement.

Sub CreateExcelObjects()
   Dim xlApp            As Excel.Application
   Dim wkbNewBook       As Excel.Workbook
   Dim wksSheet         As Excel.Worksheet
   Dim strBookName      As String
   
   ' Create new hidden instance of Excel.
   Set xlApp = New Excel.Application
   ' Add new workbook to Workbooks collection.
   Set wkbNewBook = xlApp.Workbooks.Add
   ' Specify path to save workbook.
   strBookName = "c:\my documents\xlautomation.xls"
   ' Loop through each worksheet and append " - By Automation" to the
   ' name of each sheet. Close and save workbook to specified path.
   With wkbNewBook
      For Each wksSheet In .Worksheets
         wksSheet.Name = wksSheet.Name & " - By Automation"
      Next wksSheet
      .Close SaveChanges:=True, FileName:=strBookName
   End With

   Set wkbNewBook = Nothing
   XlApp.Quit
   Set xlApp = Nothing
End Sub

The CreateExcelObjects procedure uses three Excel object variables, but only the first two are instantiated by using the Set statement. You do not need to use the Set statement to create an object variable that will be used only inside a For…Each loop. The CreateExcelObjects procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

In the next example, the top-level Outlook Application object is created by using the Set statement and the New keyword. The MailItem child object variable is created by using the Application object's CreateItem method. The Recipient child object is created by using the Add method of the MailItem object's Recipients collection.

Sub CreateOutlookMail()
   Dim olApp                  As Outlook.Application
   Dim olMailMessage          As Outlook.MailItem
   Dim olRecipient            As Outlook.Recipient
   Dim blnKnownRecipient      As Boolean
   
   ' Create new instance of Outlook or open current instance.
   Set olApp = New Outlook.Application
   ' Create new message.
   Set olMailMessage = olApp.CreateItem(olMailItem)
   ' Prompt for message recipient, attempt to resolve address, and
   ' then send or display.
   With olMailMessage
      Set olRecipient = .Recipients.Add(InputBox("Enter name of message recipient", _
         "Recipient Name"))
      blnKnownRecipient = olRecipient.Resolve
      .Subject = "Testing mail by Automation"
      .Body = "This message was created by VBA code running " _
         & "Outlook through Automation."
      If blnKnownRecipient = True Then
         .Send
      Else
         .Display
      End If
   End With
   Set olMailMessage = Nothing
   olApp.Quit
   Set olApp = Nothing
End Sub

The CreateOutlookMail procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM. Note that at the end of this procedure, each object variable is destroyed by explicitly setting it equal to the Nothing keyword. For more information about destroying object variables, see "Shutting Down an Object Created by Using Automation" later in this chapter.

****Tip   ****You can also use the New keyword to create a new instance of the object at the same time you declare its object variable. For example:

Dim olApp As New Outlook.Application

If you do this, there is no need to use a Set statement to instantiate the object. However, this technique is not recommended because you have no control over when the object variable is created. For example, if your code needs to test to see if an object exists by using a statement such as If olApp Is Nothing Then, this test will return True if you have created an instance of the object in the Dim statement. Additionally, you may not need to use an object except at the user's request. If you create an instance of the object by using New in the Dim statement, the object will be created even if it isn't used. To maintain control over when an object is created, don't use the New keyword in the Dim statement, and instantiate the object by using a Set statement at the point in your code where you need to use the object.

Single-Use vs. Multi-Use Applications

Whether you return a reference to a new instance of the Application object or an existing instance depends on whether the application's default behavior is as a single-use or a multi-use application. A single-use application causes a new instance of that application to be created whenever an object variable is instantiated in any host application. For example, Microsoft Word is a single-use application, so the following code creates a new instance of Microsoft Word regardless of how many instances of Word may already be running:

Dim wdApp aAs Word.Application
Set wdApp = New Word.Application

A multi-use application allows host applications to share the same instance of the application. The next example creates a new instance of Microsoft Outlook only if Outlook is not running when the code is executed. Since Outlook is a multi-use application, if Outlook is already running when this code is run, the object variable points to the currently running instance.

Dim olApp As Outlook.Application
Set olApp = New Outlook.Application

The following table shows the default behavior for each Office application.

You can use the GetObject function to create an object variable that references a currently running instance of a single-use application.

If you create an object variable that points to a multi-use application (Outlook or PowerPoint) and an instance of the application is already running, any method you use to create the object variable will return a reference to the running instance. For example, if Outlook is already running, the following lines of code all return a reference to the same instance of Outlook:

Dim olApp1 As Outlook.Application
Dim olApp2 As Outlook.Application
Dim olApp3 As Outlook.Application

Set olApp1 = New Outlook.Application
Set olApp2 = CreateObject("Outlook.Application")
Set olApp3 = GetObject(, "Outlook.Application")

Using the CreateObject and GetObject Functions

You can use the Set statement with the CreateObject and GetObject functions to create a top-level object variable that represents an Office application. These functions should be used only in those situations where the New keyword does not provide the functionality you need.

You use the CreateObject function to create a top-level object variable that represents an Office application in the following two situations:

• The Office application for which you want to create an Application object is not available on the local computer but is available on some other computer on your network. For example, you can run VBA code that prints reports from an Access database that is located on a network server even though Access is not installed on the computer from which the code is run. If Access is installed on the network server, you can create an Access Application object that runs on the server by specifying the name of the server in the CreateObject function's optional servername argument. For example:

  Dim objAcApp As Object
  Set objAcApp = CreateObject("Access.Application", "MyServer1")
  

  The servername argument of the CreateObject function is the same as the machine name portion of a share name. Therefore, for a share named \\MyServer1\Public, the servername argument is "MyServer1".

  To successfully run an Office application as a remote server, you must configure Distributed Component Object Model (DCOM) settings on the computer that is acting as a server, and also possibly on the client computers. To configure DCOM, run the Distributed COM Configuration utility (Dcomcnfg.exe) from the Run box on the Startup menu. For more information about configuring DCOM, search the Microsoft Technical Support Web site (http://support.microsoft.com/support) for "Configure DCOM."

• The CreateObject function is also useful when you are not sure if the Office application you want to automate will be installed on the computer that runs your code. The following example illustrates how to use the CreateObject function to make sure an application is available for Automation:

  Sub CreateObjectExample()
     Dim objApp As Object
     
     Const ERR_APP_NOTFOUND As Long = 429
     
     On Error Resume Next
     
     ' Attempt to create late-bound instance of Access application.
     Set objApp = CreateObject("Access.Application")
     If Err = ERR_APP_NOTFOUND Then
        MsgBox "Access isn't installed on this computer. " _
           & "Could not automate Access."
        Exit Sub
     End If
     With objApp
        ' Code to automate Access here.
        .Quit
     End With
     Set objApp = Nothing
  End Sub
  

  The CreateObjectExample procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM. Note that the Application object variable in this procedure is declared by using the Object data type and is late-bound to the application by using the CreateObject function. The code must be written this way because if an object variable is declared as a specific Application object type and that application is not present, the code will break.

Note The CreateObject function also must be used to work with objects from any Automation component from script. This is because scripting has no method of establishing references to type libraries to support early binding. However, for security reasons, you wouldn't typically use the CreateObject function from script to create an instance of an Office application.

You can use the GetObject function in these situations:

• You need to create a reference to a running instance of an application. For example, the following code creates a reference to the running instance of Access. If Access is not running when the code executes, a Set statement is used to create an object variable for the Access Application object.

  Sub GetObjectExample()
     Dim acApp As Access.Application
     
     Const ERR_APP_NOTRUNNING As Long = 429
     
     On Error Resume Next
     
     ' Attempt to reference running instance of Access.
     Set acApp = GetObject(, "Access.Application")
     ' If Access isn't running, create a new instance.
     If Err = ERR_APP_NOTRUNNING Then
        Set acApp = New Access.Application
     End If
     With acApp
        ' Code to automate Access here.
     End With
     ' If instance of Access was started by this code,
     ' shut down application.
     If Not acApp.UserControl Then
        acApp.Quit
        Set acApp = Nothing
     End If
  End Sub
  

  The GetObjectExample procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

  Note   If multiple instances of the application you want to automate are running, there is no way to guarantee which instance the GetObject function will return. For example, if two sessions of Access are running and you use the GetObject function to retrieve an instance of Access from code running in Excel, there's no way to guarantee which instance of Access will be used.

  There are few circumstances where it makes sense to use the GetObject function to return a reference to a running instance of an Office application. If a user opened the running instance, you would rarely want your code to be manipulating the objects in that instance of the application. However, when you use the Shell function to start an Access application (so that you can supply a password and workgroup information file to open a secured database), it does make sense to work with the running instance of Access by using the GetObject function to return a reference to the instance of Access that you started. To see an example of how to use the GetObject function to open a secured Access database, see the GetSecureDb procedure in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

  Note   You cannot use the GetObject function to return a reference to a running instance of the Office Binder.

• You also use the GetObject function when you need to open an Office file and return a reference to the host application object at the same time. The following example shows how to use the GetObject function to open an Access database from disk and return a reference to the Access application. When HTML is passed as the value for the lngRptType argument, the procedure creates a Web page from a report and displays that page in a Web browser.

  Function GetReport(Optional lngRptType As opgRptType) As Boolean
     ' This function outputs a report in the format specified by
     ' the optional lngRptType argument. If lngRptType is specified,
     ' the report is automatically opened in the corresponding
     ' application.
     ' lngRptType can be any of the following constants defined
     ' by Enum opgRptType in the Declarations section of this
     ' module:
     ' XLS = output to Excel
     ' RTF = output to Rich Text Format
     ' SNAPSHOT = output to Access snapshot report format
     ' HTML = output to HTML
     ' If lngRptType is not specified, the report is opened in
     ' Access and displayed in Print Preview.
     
     Dim acApp              As Access.Application
     Dim strReportName      As String
     Dim strReportPath      As String
     
        Const SAMPLE_DB_PATH As String = "c:\program files\" _
        & "microsoft office\office\samples\northwind.mdb"
     
     strReportName = "Alphabetical List of Products"
     strReportPath = "c:\my documents\"
     ' Start Access and open Northwind Traders database.
        Set acApp = GetObject(SAMPLE_DB_PATH, "Access.Application")
     With acApp
        ' Output or display in specified format.
        With .DoCmd
           Select Case lngRptType
              Case XLS
              .OutputTo acOutputReport, strReportName, _
                 acFormatXLS, strReportPath & "autoxls.xls", True
              Case RTF
              .OutputTo acOutputReport, strReportName, _
                 acFormatRTF, strReportPath & "autortf.rtf", True
              ' Snapshot Viewer must be installed to view snapshot
              ' output.
              Case SNAPSHOT
              .OutputTo acOutputReport, strReportName, _
                 acFormatSNP, strReportPath & "autosnap.snp", True
              Case HTML
              .OutputTo acOutputReport, strReportName, _
                 acFormatHTML, strReportPath & "autohtml.htm", _
                 True, "NWINDTEM.HTM"
              Case Else
              acApp.Visible = True
              .OpenReport strReportName, acViewPreview
           End Select
        End With
        ' Close Access if this code created current instance.
        If Not .UserControl Then
           acApp.Quit
           Set acApp = Nothing
        End If
     End With
  End Function
  

  The GetReport procedure is available in the modSetObjVariable module in Automating&IDE.doc in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

Working with Documents That Contain Startup Code

Using Automation to open a document does not prevent a document's startup code from running. Startup code can be defined in various ways in Office applications, as explained in the following table.

Note   PowerPoint and FrontPage documents don't have a way to define startup code.

Because startup code may display message boxes or modal forms that act as dialog boxes, these message or dialog boxes may prevent your code from proceeding until a user closes or responds to them. If you have startup code in an Excel workbook or Access database that you don't want to run if the document is opened programmatically from another application, you can often use the UserControl property of the Application object to determine how a document is being opened and then act accordingly. If you can't use the UserControl property, you may need to use a SendKeys statement to send keystrokes to close the message or dialog box.

In Excel, the UserControl property will return False only when the document or workbook is opened from Automation by using a hidden instance of the Excel Application object (Application.Visible = False). For example, the following code defined in an Excel workbook's Open event procedure will run only if the workbook is opened by a user or a visible instance of the Excel Application object. If you open the workbook by using a hidden instance of the Excel Application object from code running in another application, the message box won't be displayed.

Private Sub Workbook_Open()
   Dim strMsg As String
   
   strMsg = "This message was triggered by this workbook's " & _
          "Open event." & vbCrLf & _
          "It won't be displayed if this workbook " & _
          "is opened by using a hidden" & vbCrLf & _
          "instance of the Excel Application object " & _
          "from Automation code."
   
   ' If opened through Automation by using a hidden instance,
   ' the UserControl property will be False.
   If Application.UserControl = True Then
      MsgBox strMsg
   End If
End Sub

The Workbook_Open procedure is available in the ThisWorkbook module in AutomatingWord.xls in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

Note In Word 2000 and Word 97, there is no way to prevent Open event code from running with the UserControl property. If Word is visible to the user, or if you call the UserControl property of a Word Application or Document object from within a Word code module, this property will always return True. However, you can still use the Word UserControl property from Automation code (that creates a hidden instance of Word) running from another application to determine if a document was opened programmatically or by the user.

In Access, you don't have to check or keep track of whether the instance of the Application object is hidden or visible because the UserControl property is False whenever the application is started from code. To control whether code in the startup form's Open event is executed, Access provides a Cancel argument for the Open event. As shown in the following example, you can set the Cancel argument to True to keep a startup form from opening if you open the database by using Automation code:

Private Sub Form_Open (Cancel As Integer)
   ' If database is opened from Automation,
   ' cancel the Open event of the form.
   If Application.UserControl = False Then
      Cancel = True
   Else
   ' Any startup code that needs to run when the
   ' database is opened by a user goes here.
   End If
End Sub

The Form_Open procedure is available in the Form_frmStartup module in Startup.mdb in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM.

You can also use the UserControl property of the Access Application object to control whether actions in a database's AutoExec macro will run when the database is opened from another program by using Automation. To do this, you must enter Application.UserControl = True in the Condition column for each action you want to cancel. (To display the Condition column, click Conditions on the View menu.) To see an example of an AutoExec macro that cancels an action when the database is opened through Automation, open Startup.mdb in the ODETools\V9\Samples\OPG\Samples\CH04 subfolder on the Office 2000 Developer CD-ROM and then open the AutoExec macro in Design view.

Tip   You can also use COM add-ins to implement a startup form or code. COM add-ins support events that you can use to determine how an application was loaded before connecting the add-in. For more information about COM add-ins, see Chapter 11, "Add-ins, Templates, Wizards, and Libraries."