Creation of Object Variables to Automate Another Office Application

Article
09/14/2006

Working with the objects from one Microsoft® Office application to another Office application through Microsoft® Visual Basic® for Applications (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 might choose to use the CreateObject or GetObject function to create an object variable.

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 Microsoft® 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 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

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

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. When 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, Microsoft® Excel and Word also make it possible for 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, this way:

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

Similarly, Excel makes it possible for 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 such as 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 such as 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 must 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 might 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.

Object type	Class name
Access application	Access.Application
Excel application	Excel.Application
Excel workbook	Excel.Sheet Excel.Chart
FrontPage application	FrontPage.Application
Outlook application	Outlook.Application
PowerPoint application	PowerPoint.Application
Word application	Word.Application
Word document	Word.Document

Share via

Creation of Object Variables to Automate Another Office Application

See Also

Additional resources