| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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:
-or-
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.
| Object type | Class name |
| Access Application | Access.Application |
| Office Binder | OfficeBinder.Binder |
| 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 |
You can see the complete object models for each Office application in the Microsoft Office 2000 Object Model Guide.
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."
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.
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.
| Application | Application type |
| Access | Single-use |
| Binder | Single-use |
| Excel | Single-use |
| FrontPage | Single-use |
| Outlook | Multi-use |
| PowerPoint | Multi-use |
| Word | Single-use |
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")
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:
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."
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:
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.
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.
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.
| Application | Startup code location |
| Word | Startup code is contained in the event procedures for the Open or New events in the ThisDocument module of a document or template. |
| Excel | Startup code is contained in the event procedure for the Open event in the ThisWorkbook module of a workbook or template. |
| Outlook | Startup code is contained in the event procedures for the Startup event in the ThisOutlookSession of the local Outlook VBA project. |
| Access | If you create an Access macro named AutoExec, this macro's actions will run on startup. You can also place startup code in the event procedure for the startup form's Open event. To specify a form to be opened on startup, use the Startup command on the Tools menu. |
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."