Developing Integrated Office Solutions, Part 1

  • Article

 

David Shank
Microsoft Corporation

May 4, 2000

When it comes to programmatically integrating Microsoft® Office applications, I'm reminded of the old adage that a thing can be "greater than the sum of its parts." As powerful as each Office application is on its own, you can amplify both power and functionality by integrating the features from two or more Office applications into a single custom solution.

The process of using Visual Basic® for Applications (VBA) from one Office application to work with the objects in another Office application is called Automation. You can think of the objects exposed by an Office application as a set of building blocks that you can incorporate into your own solution—leveraging the design, development, and testing that went into producing them, while shortening your development time.

This month, I'll give you an overview of Automation, provide some practical examples to help illustrate the concepts, and point you to the wealth of resources where you can get additional information about using Automation in your own custom solutions.

What Is Automation?

Automation is the Component Object Model (COM) technology (formerly called "OLE Automation") that allows a developer to use VBA code to create and control software objects exposed by any application, dynamic-link library (DLL), or ActiveX® control that supports the appropriate programmatic interfaces. The key to understanding Automation is to understand objects and object models: what they are, how they work, and how they work together.

The COM software architecture allows software developers to build their applications and services from individual software components. COM components consist of the physical, compiled files that contain classes, which are code modules that define programmable objects.

The Windows operating system and Office suite of applications are examples of products that have been developed by using the COM software architecture. Just because software is developed by using COM doesn't necessarily mean that it can be programmed by using VBA. However, if an application or service supports the COM technology known as Automation, it can expose interfaces to the features of its components as objects that can be programmed from VBA, as well as many other programming languages. To support Automation, an application or service must allow exposure of its custom interfaces through either or both of two methods:

  • By providing the IDispatch interface. In this way, the application or service can be queried for further information about its custom interfaces. Applications and services that support the IDispatch interface provide information about their custom interfaces at run time by using a method called late binding.
  • By allowing direct access at design time to the member functions in its virtual function table, or vtable, that implement its interfaces. Applications and services that support direct access to custom interfaces support early binding.

An application can be said to support Automation if it supports either one, but not necessarily both, of these methods. The Office suite of applications provides support for both methods.

You can think of Automation as a nervous system that makes programmatic communication and feedback between applications and components possible, and as "glue" that lets you integrate features from Office applications and other software components into a custom solution.

Understanding the Basics

You work with the objects exposed by another Office application by referencing the application you want to automate. This lets your code "see" the objects exposed by the other application. There are two ways to reference another application—and the method you use will determine whether you have an early-bound or a late-bound reference.

Setting a Reference

When working with an Office application's type library, you will always create early-bound references. This lets you use the Object Browser and the Visual Basic Development Environment (VBE) Intellisense features, such as automatic statement completion, while writing your code. In addition, using an early-bound reference exposes Help topics for exposed objects, and provides better performance at run time than using late-bound objects. Early binding is the friendly name for what C programmers call virtual function table binding, or vtable binding. To use early binding, the host application must establish a reference to a type library (.tlb) or an object library (.olb), or an .exe, .dll, or .ocx file that contains type information about the objects, methods, properties, and events of the application or service you want to automate.

You create an early-bound reference by setting a reference to an Office application type library using the VBE References dialog box. You open this dialog box by using the References command on the Tools menu. Each Office application contains several references that are set by default. The following picture shows the Word References dialog box, where I have added a reference to the Outlook type library. The first five references shown are those that Word sets by default.

Figure 1. The References dialog box

Creating an Object Variable

You can initialize the object variable by using the CreateObject or GetObject function, or by using the New keyword if the application supports it. All Office applications can be initialized by using the New keyword as shown in the following code fragment:

Dim olApp As Outlook.Application
Set olApp As New Outlook.Application
With olApp
   ' Code to work with Application object goes here.
End With

Although you can use the New keyword in the DIM statement, it is not recommended. Dimensioning an object variable and then instantiating it by using the SET statement, as shown above, gives you more control over how and when the variable is created.

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. 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. Once you have created this explicit object reference, you will have access to all the other objects exposed by the application as children of the Application object.

Destroying an Object Variable

Although a variable declared within a procedure is destroyed when the procedure terminates, it is good programming practice when working with object variables to explicitly quit the application being automated and destroy the object variable by setting it equal to the Nothing keyword. The following sample procedure illustrates this technique:

Sub SendDataToWord()
    ' Dimension object variables.
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document

    ' Instantiate object variables.
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Add

    ' Add text to new document and then save it.
    With wdDoc
        .Range.Text = "Automation is cool!"
        .SaveAs "C:\My Documents\AutomateWord.doc"
        .Close
    End With

    ' Destroy object variables.
    Set wdDoc = Nothing
    wdApp.Quit
    Set wdApp = Nothing
End Sub

To try this procedure, copy the code above into a code module in any Office application, set a reference to the Word object library, place the cursor anywhere in the procedure, and press F8 to step through the code as it executes.

Building a Real-World Example

This example uses a Word template that contains a company letterhead and VBA code that retrieves name and address information from the Outlook Contacts folder. When a new document is created based on this template, the user can select the recipient of the letter from the list of contacts and have the name and address automatically inserted into the document.

The following picture shows what the user would see when creating a new document:

Figure 2. Example of a Word document template automated to retrieve Outlook data

I won't go into the details of how you create the letterhead template here. Instead I will focus only on how to use Automation from Word to retrieve data from Outlook.

The template's Document_New event procedure calls the GetOutlook procedure that retrieves contact information from Outlook and saves the data to an array. The GetOutlook procedure first creates an object variable representing the Outlook Application object, and then uses that object to instantiate the NameSpace and MAPIfolder object variables needed to get at the contacts data. Once at the Contacts folder, the MAPIFolder object's Restrict method is used to return a collection that contains only contacts where the BusinessAddress property has been set. Then, for each contact with a business address, the contact data is added to the global array named gavarContactsArray. That array is then sorted so the items are arranged alphabetically by calling the QuickSort procedure, which is not shown here.

Sub GetOutlook()
    ' This procedure retrieves all contacts from the Outlook
    ' Contacts folder where there is a Business Address and
    ' places those items in an array called gavarContactsArray.
    ' When the frmShowAllContacts form is loaded, it uses data
    ' in the gavarContactsArray array to fill the combobox so the
    ' user can select the contact to use for the letter address.

    Dim olapp           As Outlook.Application
    Dim nspNameSpace    As Outlook.NameSpace
    Dim fldContacts     As Outlook.MAPIFolder
    Dim objContacts     As Object
    Dim objContact      As Object
    Dim intCntr         As Integer
    Dim strZLS          As String

    On Error GoTo GetAll_Err
    ' Initialize zero-length string variable
    ' used in the Restrict method argument.
    strZLS = ""
    ' Get reference to the Outlook Contacts folder.
    Set olapp = New Outlook.Application
    Set nspNameSpace = olapp.GetNamespace("MAPI")
    Set fldContacts = nspNameSpace.GetDefaultFolder(olFolderContacts)
    Set objContacts = fldContacts.Items.Restrict("[BusinessAddress] <> '" & strZLS & "'")

    ' Resize the array to the number of Outlook Contacts.
    ReDim gavarContactsArray(objContacts.Count - 1)

    For Each objContact In objContacts
        ' Add only entries that include a business address.
            gavarContactsArray(intCntr) = IIf(Len(objContact.FullName) > 0, _
                objContact.FullName & vbCrLf, "") & IIf(Len(objContact.CompanyName) > 0, _
                objContact.CompanyName & vbCrLf, "") & objContact.BusinessAddress
            intCntr = intCntr + 1
    Next objContact

    ' Sort the array.
    QuickSortArray gavarContactsArray

GetAll_Bye:
    Exit Sub
GetAll_Err:
    MsgBox Err.Description, vbOKOnly, "Error = " & Err.Number
    Resume GetAll_Bye
End Sub

When the form shown in the previous picture is displayed, the contact names are loaded into the combo box using the FillUsingOutlookData procedure:

Sub FillUsingOutlookData()
    ' This procedure fills an array with data from an
    ' Outlook Contacts folder.
    Dim intCurrentContact As Integer

    ' Fill the combo box with names from the array.
    For intCurrentContact = 0 To UBound(gavarContactsArray) - 1
        cboContacts.AddItem Left(gavarContactsArray(intCurrentContact), _
            InStr(gavarContactsArray(intCurrentContact), _
            vbCrLf) - 1)
    Next intCurrentContact
    ' Display the first item in the list.
    cboContacts.ListIndex = 0
    ' Update the address information for the
    ' displayed name.
    UpdateAddress
End Sub

Finally, the address information for the name displayed in the combo box is added to the text box on the form by calling the UpdateAddress procedure:

Sub UpdateAddress()
    Dim intIndex As Integer
    ' Use the selected item in the combo box as the index
    ' into the array of contact names and addresses and display
    ' the address information in the txtAddress text box.
    intIndex = cboContacts.ListIndex
    txtAddress.Text = Mid(gavarContactsArray(intIndex), _
        InStr(gavarContactsArray(intIndex), vbCrLf) + 2)
End Sub

Once the user has selected the name to use as the recipient of the letter and has clicked OK on the form, the name and address data are inserted in the document at the location of a bookmark named Address.

This example illustrates a simple yet powerful technique that adds to contact management capabilities of Outlook with the document creation capabilities of Word to create a custom solution that neither application provides natively. This is where the power of Automation really shines.

Where To Get More Info

Here are some additional resources you can use to explore working with Office Automation:

David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners still living in the area.