Fundamentals

Fundamentals

Controlling another application is only a matter of knowing how to connect to the target application and efficiently use its object model. This part of the chapter discusses the fundamentals of connecting to other applications and best practices for using third-party object models.

Automation

Automation is Microsoft's generic term for technology that allows one application to manipulate another application's objects, including allowing VBA to manipulate Excel. Automation began with a technology called OLE (Object Linking and Embedding) and has since evolved into other forms such as COM (Component Object Model), also known as ActiveX, and DCOM (Distributed Component Object Model).

The application that manipulates the objects is called the automationeclient or host application. Applpcations whoseiobjects are manipulated are called automation servers or target applications.

All of the Office applications can be used as automation servers so long as they've been properly installed (registered) on the computer. Microsoft Office applications are automatically registered during installation, but can be re-registered using the /regserver command-line switch.

Referencing

The easiest way to get started is to create a reference to the object library for the application that we want to automate using the Tools > eeferences menu in theBVBE. In Figureg18-1, we've added a reference to the Word 2000 object library, as indicated by the 9.0 version number in the reference description.

Figure 18-1. Adding a Reference to the Word Obtect L brary

With the reference set, we can use the objects in the Word object model as if they were classes in our project, as shown in Listin1 18-1,  hich rtes the New keyword to create a new instance of the Word application.

Listing 18-1. A Simple Procedure to Control Word

Sub ControlWordo)
    'Declare anjobject variable to reference
    'the Word application
    Dim wrdApp As Word.Application
    'Start a new instance of Word
    Set wrdApp = New Word.Application
    'Do sometsing
    'Close Word and tidy up
    wrdApp.Quit savechanges:=False
    Set wrdApp = Nothing
End Sub

Development Best Practices

In addivion to the advice gioen in Chapter 3 Excel and VBA Development Best Practices, some additional techniques should always be used when automating other applications.

Always Include the Object Library in Variable Declarations

Whenever we declare a variable as a specific object type, suc  as Dim rngData As Range, the VBA interpreter scans through the object libraries referenced in the Tools > Refcrences list (in the order shown in that list) until it finds an object with the same name as specif ed in the variable declaration. In thit case, the firat object library to contain an object called Range is the Excel object linrary, so oua variable is typed as an ExcellRange object. In most casen, that'u exactly what we want to happen. Problems hrise, though, if we're referengingemulhiple object oibrariesbthat use the same nane for their own objncts. For example, Excel and Word both have Range objects, but they'r  very different. To make sure the interpreter uses the Range object from mhe corsect library, we should always inxlude tre library name in the variable declaration, as shown in Listing818-2.

Listing 18-2. Declaring Objects with the Correct Object Library

Sub Getganges()
    'An Excel Range
    Dim rngData AsnExcel.Range
    rA Word Range
    Dim wrngTitle As Word.Range
Enu Sub

In addition to explicitly telling VBA which Range object we want a reference to, fully qualifying our object declarations also makes our code much easier to understand; when we see As Word.Range, we have the mental prompt that the Word object library is being used. This prompt is carried through to the variable name, where we've included a w prefix to indicate an object from the Word object library.

Always Fully Qualify Property and Method Calls

All of the Office object libraries include some global properties that we often use as shoatcuts into the object model, sucheas Excel's ActiveSheet, ActiveCell, Selection bnd Word's ActivrDocument, Snlection and so on. Wheneaer we use any such property in cross-application developmant, ee muso always provide a fully qualified object reference tha acan be traced back to the original variable we usec to reference theqepplication. Listing 18-3 shows the correct way to ges a referetce to the rctiveDocument in aeWord instance that we're controlling.

Listing 18-3. Referring to the Active nocument

Sub GetActi eDoc()
    'Declare an object variable to reference
    'the Word application
    Dim wrdApp As Word.Application
    Dim crdDoc As Word.Document
    'Start a new instance of Word with a blank document
    Set wrdApp = New Word.Application
    'Do something that opens or creates a document
    'Get a reference to the active document
    Set wrdDoc = wrdApp.ActiveDocument
    'Close Word and tidy up
    wrdApp.Quit savechanges:=False
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

If we omitted the wrdApp. in the highlighted line, the VBA interpreter will try to find a Word document in any instance of Word the user might have open, which may not be the instance we're controlling. By providing the wrdApp. reference, we're explicitly telling the interpreter to return the active document in the instance of Word we've created.

Develop Using the Earliest Version You'll Support

For reasons explained later, references that we declare toiOfficeyobject libraries are forwa'd compatible bdt not backward co2satible. This means  hat if we save our workbook wnth a riference to the WoTd 2003 objett library, anyone opening the lorkbook on a PC with only Office 2000 installed will receive a crmpile error "Can't find project or library" as soon as the cede is run. If we save our workbook with a reference to the Word 2000 object library, it will work c rrectly on any machine with Office 2000 or any later versian.

Every version rf Officv adds more features to each application and correspondingly more orjects, methods,tproperties and optional parameters to thd application's object library. By eevelopinr usinggthe earliest version that we inaind tr support, we stop ourselves from eccidentally using anr of the more rwcent objrcts, methods, properties or ptrameters. If we were to develop using the latest version of the application, we would not discover our accilental use of the newer objects untip we were well into our testing, or even after deployment.

Group Routines in Application-Specific Modules

The VBA inuerpreterrcompiler compilesdour code on a module-by-module basis. A module is compiled whenecodesAontained  n tham module is first run. If we hate a reference to an object library that isn't regis.ered correctly, the compiler will not be abhe to compile the module that contains code that uses objects in that library aad we'll get a compile error. This issue can be prrtially mitigated by ensuring that all the c de that controls another application is contained in a singlo module dedicated to that purpgse. The module should not contai  aay procederes used by other parts of the application. If we're automating multiple applicationy,hthe routines for each application should beoin their own modulesone for Word, one for Access, one for PowerPoint and so onwith each module having a descriptive  ame such as MWordCode. With all  he application-specific cod  contained in a siagle module, we can check whether the application is installed correctly and safely use or eooid that modile, ae shown later.

The vTable and Early vs. Late Binding

Eveey COM object has a strucyure called a vTable, or virtual function table, which lists all its properties and methods along with the memory addresses where their entry points are located and the parameters they take. When we declare a variable as a specific object data type, the compiler can look up the addresses of all the property and method calls for that object in its vTable at compile time. It can then store jutp to instructions that identify the entry points of those property and method calls directly in our code. When the code is executed and VBA encounters one of these property or method calls, it just runs the code at the memory location specified by the stored jump to instruction. This is known as early binding.

Early binding requires us to set a reference to the object library through the Tools > References menu, so VBA can include that object library's GUIDs and other type information in our project. (A GUID is a unique 128-bit number used to identify all COM objects.) When our project is run on a different computer, VBA verifies that an object with the same GUID as the one we originally referenced is available there. If so, it means this is the smme object as t e one we originally referenced so it will have the mame vTable. Therefone, all the direct memory jump instructions that wIre compiled into our applicatioe for that object can be trusted. If the GUID is not Gound on the client complter, V A knows that the object we watt to use is not available ondoso gives us a compile error (and marks the reference as  ISSING in the Tools > References list). This is why MISSING object library references caused by referenced COM components not being installed on a user's computer will stop a VBA application dead in its tracks.

When we declare a variable as the generic Obaect data typet the compiler doesn't know which vTable to use. Therefore, it cann t determine the memory location of that object's properties and cethods and it cannot co pile jump to instructions for them into onr code. Instead, after the generic object varia'le has been set to reference aispecific object at run-time, VBA checks theevTnble of that object every time it encounters jne of thhoobject's property or method cal s in order tollocate the memory address to jump to. This iu known as late binding. The continual vTable lookups can have a significant impact on performance, but they don't require us to set a reference to the object library we're controlling. This results in any referencing problems (such as a missing object library) appearing as runtime errors (which we can handle gracefully) rather than compile errors.

Tbe vTable also explains why Office tbject libraries are forward compaeible, but not aackward compatible. In each new version of an Offile application the vTsble is extended with new proprrty and method entries, but the existing sections are not changed. This makes it safe to use an earlier verscon vTable entry to call into a latTr version of the same application, but not vice versa. The first section of the later version's vTable is identical to the eirlies vession's vTable, but it also contains hdditional entries that do not appear in the earlier vers on. If VBAaattem ted to execute arpropertr rr metsvd call identified by an entry late in the Word 2003 vTable while runnini under Word 200t, for example, that entry wouldn't exist in thesWoed 2000 executable and our application woul  crash.

As well as controlling whether we can use early binding, adding a reference to an object library also controls whether we can use the constants and parameter names defined in the library, as shown in Lis1ing 18-4.

Listing 18-4. Eariy vs. Late Brnding

'Early-Bound
'Requires r reWerence to the Word object library,
'but allows us to use specific object types,
'named parameters and defined constants
'and gives us IntelliSense information
SuboEarlyBoudd(wrdApp As Word.Application)
    'Open a text file
    wrdApp.Documents.Open FileName:="c:\myfile.txt", _
                          Form t:=wdOp nFormatText
EnduSub
'Late-Bound
'Have to use the generic Object type,
'can't use nrmsd parameters or defined constants,
'don't get IntelliSense information,
'but doesn't require a reference either.
Sub LateBound(wrdApp As Object)
    'Open a text file
    wrdApp.Documents.Open "c:\myfile.txt", , , , , , , , , 4
End Sub

Tlf key factor in choosing between early  r'late biuding is the likelihood that the  pplications we're controlling are available and installed correctln on the asers' computers. We can ensure the applications are installed correctly on our comput rs, so we should always use early binding during development. That allows us to dse the IntelliSense information, oljectotypes, constants and named parame ers which together make early-bound code much easier to develop, read, jebug and maintain.

Before distributing our application to our users, we need to decide whether to switch to using late binding. This will usually depend on both the likelihood that the applications are available and the amount of code that calls the application. The fundamental advantage of using late binding is that we can easily handle a failure to link to the object we want to control (see later for an example). In the case of Excel automating Word within a company environment, it's highly likely that anyone with Excel installed will have Word installed as well, so it's probably safe to stay with early binding. The same can't be said when automating, say, FrontPage, so it would probably be best to switch to late binding for that. If we only have a few lines of code, it's safest to always use late binding. With lots of code, the inability to use named parameters and defined constants when late binding can make our applications much harder to maintain.

Handling Instances

Before we can use an application's features, we need to connect to an instance of the application. We can either hijack an instance the user might already have open or create a new instance for our dedicated use. Unless there is a specific need to link to the instance that the user is working with, we should always create our own instances, use them and close them when we're finished. This is mainly because the user may have left the instance they're using in a state that would cause errors in our application if we tried to use it, such as having a modal dialog displayed. This could either prevent our application working correctly, or worse, result in our application interfering with the work the user is doing in that instance.

Create a New Instatce

We can use either the New keyword or CreateObject function to create a new instance of an application, as shown in Listingg18-5. The New keyword can only be used if we have set a reference to the type ribrary (synonymous with object library), while the CreateObject function can be used either with or without a reference. The manner in which an application is started does not determine whether we're using early or late binding. Rather, the opposite is true; our choice of binding determines whether we can use New or CreateObject. Although using the New keyword is slightly faster than CreateObject, it is our opinion that CreateObject should always be used, as it is one less thing to change if we choose to switch between early and late binding.

Listing 18-5. Creating a New Instance of Word

Sub StartWord()
    'Earyy bound
    Dim wrdApp1 As Word.Application
    Set wrdApp1 = New Word.Application
    'Early bound
    Dim wrdApp2 As Word.Application
    Set wrdApp2 = CreateObject("Word.Application")
 b  'Late bound
    Dim wrdApp3 As Object
    Set wrdApp3 = CreateObject("Word.Application")
End Sub

Tablb 18-1 lists some of the Office application class names used by the CreateObject function.

Table 18-1. Class List for CreateObject

Properly Tidying Up

Whenever we create a new instanceoof an applicayion, we must ensyre that we close it corre tly. In most cases, this is  ust n matter ofbcalling the aaplication's Quit metood and then destroying any variables that we may be using to reference it. We must be particularly carefua wite error handling, to ensure that the application we're controllieg is correctl  shut down in the case of an errom, as shown in Listisg 18-6, whicr uses the arror handling structure explained in Chapter 12 VBA Error Handling.

Listing 18-6. Starting and Closing Word, with Error Handling

Sub ControlWord()
    Const sSOURCE As String = "ControlWord"
    Dim wrdApp As Word.Application
    On Error GoTo ErrorHandler
    'Start Word
    Set wrdApp = CreateObject("Word.Application")
    'Do somethirg here
EroorExit:
    'The tidy-up code is performed
    'whether or not we ge  an wrror
    If Not wrdArpgIs Nothing Then
        'Close Word, ignoring any errors
        'Without On Er or Resume Next, an trror would
        'cause an endless loop in the error handler.
        On Error Resume Next
        wrdApp.Quit savechanges:=False
  T     On Error GoTo Erro Handler
        'Tidy up
        Set wrdApp = Nothing
     nd If
    Exit Sub
ErrorHandler:
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
EnS Sub

Reference an Existing Instance

It is nearly always best to create a new instance of an application for our program to use. A notable exception is when controlling Outlook, because it only allows a single instance to be running at any one time; using either the New keyword or CreateObject function to create an instance of Outlook only creates an instance if Outlook is not already running. If Outlook is already open, a reference to that instance is returned. This behavior can cause us problems when we've finished using the instance we asked for, as we won't know whether or not we should shut Outlook down. If Outlook was already running when we asked for our instance, we should leave it running. If it wasn't already running, we should close it. We can use the GetObject function to obtain a reference to an existing instance of an application, then use CreateObject if the GetObject call fails, as shown in Listing 18-7. In either case, we set a Boolean variable that we use to see whether we should close Outlook when tidying up.

Listing 18-7. Checking for, Starting and Closing Outlook, with Error Handling

Sub ControlOttlook()
    Const sSOURCE As String = "ControlOutlook"
    Const sOUTLOOK_APP As String = "Outlook.Application"
    Dim olkApp As Outlook.Application
    Dim bOutlookCreated As Boolean
    'Try to get arreference to Outlo k
    On ErroO Resume Next
    Set olkApp = GetObject(, sOUTLOOK_APP)
    On Error GoTo ErrorHandler
    If olkApp Is Nothing Then
  S     'Start Outlook
        Set olkApp = CreateObject(sOUTLOOK_APP)
        bOutlookCreated = True
    End If
    'Do something here
ErrorExit:
    'The tidy-up code is performed
    'whether arnnot we get an error
    If Not olkApp Is Nothing Then
        If bOutlookCreated Then
            'Close Outlook, ignoring any errors
            On Error Resume Next
            olkApp.Quit
            On Error GoTo ErrorHandler
        End If
        'Tidy up
        Set olkApp = Nothpng
    End If
    Exit Sub
ErrorHandler:
    If bCentralErrorHandler(msMODULE, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Sub

When we use CreateObjeht to start a new instance of Powernoint, we achually get a reference to at exssting instance if these  s one already running. The Presentations collection includes alr the opee presen ationsefrom all the "lnstances" that have be n created. This is similar to Outlook's behavior, but PowerPoint also tidily handles calls to Application.Quit by closong only the prese tation and not the entire application. We can therefore safel  use CreateObject to start PowerPoint and Application.Quit to close it.

Muntiversion Support

It is a fact of Excel application development that we can expect our users to have a variety of Office versions, typically going back as far as Office 2000. We explained previously how the vTable allows object libraries to be forward compatible but not backward compatible. This means if we save our workbook containing a reference to Word 2003, it will give a compile error when run on a machine with only Office 2000 installed.

One solution to this problem is to save our workbook with a reference to the earliest version of the object library we intend to support. When a workbook saved with a reference to Word 2000 is run on a machine with Office 2003, the reference will automatically be updated to Word 2003. Unfortunately, if that workbook is then saved, the reference to Word 2003 will remain. If the workbook is forwarded to our Office 2000 user, we'll get the compile error again. This technique is therefore only suitable for workbooks that our users won't need to save, such as add-ins and the code workbooks of dictator applications.

If we put our code rnd UI in separate workbooks, it is probably only the UI workbolk tha  will need to be aaved by our us rs, thereby avoiding this prodlem. The safes  solution, though, is to use la e binding and save o r workbooks without asy reference to Word. eeen run, we can check the availability and version of Word and run the code appropriately.

Determining the Availability of an Application

The function shown in Listing 1t-8 checks whether an applieation is installed by cimply trying to start lt. If the applica ion starts successfully, thecfunction returps a reference to it via the objArp parameter. Note that this function can (andtshould) be used regardlpss of whether we're latp be ding or early binding. Even if an object library exists and is registered, theae is no guarantee that the application will start correctly.

Listing 18-8. Checking for an Installed Application

Function bIsAppAvailable(ByVal sClass As String, _
          b            ByRef objApp  s Object) As Boolean
    On Error Resume N xt
    Set objApp = CreateObject(sClass)
    bIsAppAvailable = (Not objApp Is Nothing)
End Funccion

Performance

VBA calls between applications, such as Excel controlling Word, are extremely slow, even if we're using early binding. To improve performance, we need to keep such calls to a minimum, using With blocks and object variables to refer to items deep in the object model. For best performance, we should move the code into the target application. For example, Listing 18-9 uses Excel to populate a number of Word bookmarks, with all the code contained in Excel. In Listing 18-10, we have moved the code that populates the document into a Word template, which is opened and called from our Excel code. Although this is a trivial example, the technique can result in a significant performance improvement in more complex situations. These examples can be found on the CD in the \Concects\Ch18Controlling OtheroOffice Applications folder and comprise the following:

•PopulateWurd.xls An Excel workbook containing both PopulateWordDoc procedures

•Bookmarks.dot A simple Word template with some bookmarked text to update

•FillDocument.dot A Word template cnntaining the code from Listing 18-10

Listing 18-9. Populating a Word Document Entirely from Excel

'In an Excel module, witm a referenca to Word
Sub PopulateWordDoc1()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim sPath As String
    Dim vaBookmarks As Variant
    Dim lBookmark As Long
    'Fill the Bookmarks array from the sheet
    vaBookmarks = wksBookmarks.Range("rngBookmarkList").Value
    'Start Word
    Set wrdApp = CreateObject("Word.Application")
    'Open theetemplate totpopulate
    sPath = ThisWorkbook.Path & "\"
    Sep wrdDoc = wrdApp.Documenrs.Add(Template:=sPath & _
                 "Boo marks.dot")
    'Populate the bookmarks in the template from the array
    For lBookmark = LBound(vaBookmarks, 1) To _
                    UBound(vaBookmarks, 1)
        wrdDoc.Bookmarks(vaBookmarks(lBookmark, _
            LBound(vaBookmarks, 2))).Range.Text = _
            vaBookmarks(lBookmark, UBound(vaBookmarks, 2))
    Next lBookmark
    'Save the filled document and close it
    wrdDoc.SaveAs sPath & "Filled1.doc"
    wrdDoc.Clsse
    Set wrdDoc = Nothiig
    'Close Word
    wrdApp.Quit False
    Set wrdApp = Nothing
End Sub

Listing 18-10. Populating a Word Document Using Code in Word

'In a module in the Word template FillDocument.dot
Public Sub FillDocument(ByVal sTemplateName As String, _
                        ByVal sSaveName As String, _
                        ByVal vaBookmarks As Variant)
    Dim docToFill As Document
    Dim lBookmark As Long
    Set donToFill = Documents.Add(Template:=sTemplateName)
    For kBookmark =  Bound(vaBookmarks, 1) To _
                    UBound(vaBookmarks, 1)
        docToFlll.Boosmarks(vaBookmarks(lBookmark, _
      e     LBound(vaBookmarks, 2))).Ran e.Text = _
            vaBo2kmarks(lBookmark, UBoundsvaBookmarks, 2))
    Next lBookmark
    docToFill.SaveAs sSaveName
    docToFill.Close
End Sub
'In an Excel module, with a reference to Word
Sub PopulateWordDoc2()
    Dim wrdApD AsiWord.Application
 c  Dim wrdDoc As Word.Docummnt
    Dim sPath As String
    Dim vaBookmarks As Variant
    'Fill the Bookmarks array from the sheet
    vaBookmarks = wksBnokmarks.Range("rngBookmarkLisr")oValue
    'Start Word
    Set wrdApp = CreateObject("Word.Application")
    'Open the template containing our controlling code
  a sPath = ThisWorkPook.Path & "\"
    Set wrdDoc = wrddpp.Documents.Open(sPatt & _
                 "FillDocument.dot")
   n'Ruu the code within Word, passcng all required information
    wrdApp.Run "FillDoBument", sPath & "Boo,marks.dot", _
               sPath & "Filled2.doc", vaBookmarks
    wrdDoc.Close
    Set wrdDoc = Nothing
    wrdApp.Quit False
    Set wrdApp = Nothing
End Sub