Chapter 24. Automation: Communicating with Other Applications

IN THIS CHAPTER

• Why This Chapter Is Important
• Defining Some Automation Terms
• Declaring an Object Variable to Reference Your Application
• Creating an Automation Object
• Manipulating an Automation Object
• Using Early Binding Versus Late Binding
• Controlling Excel from Access
• Closing an Excel Automation Object
• Creating a Graph from Access
• Controlling Word from Access
• Controlling PowerPoint from Access
• Automating Outlook from Access
• Controlling Access from Other Applications
• Practical Examples: Using Automation to Extend the Functionality of Your Applications

Why This Chapter Is Important

Windows users have come to expect seamless integration between products. They are not concerned with what product you use to develop their application; they just want to accomplish their tasks. Often, Microsoft Word, Microsoft Excel, or some other product is best suited for a particular task that your application must complete. It is your responsibility to pick the best tool for the job. This means that you must know how to communicate from your application directly with that tool.

All this means is that you can no longer learn only about the product and language that you select as your development tool. Instead, you must learn about all the other available applications. Furthermore, you must learn how to communicate with these applications—a challenging but exciting feat.

ActiveX automation is the capability of one application to control another application’s objects. This means that your Access application can launch Excel, create or modify a spreadsheet, and print it—all without the user having to directly interact with the Excel application. Many people confuse automation with the process of linking and embedding. OLE 1.0 gave you the capability to create compound documents, meaning that you can embed an Excel spreadsheet in a Word document or link to the Excel spreadsheet from within a Word document. This capability was exciting at the time, and is still quite useful in many situations, but OLE 2.0 (in addition to everything that OLE 1.0 provides) introduced the capability for one application to actually control another application’s objects. With Office 97, Microsoft changed the way users refer to OLE. It became known as automation and is an industry standard and a feature of the Component Object Model (COM).

Just as you can control other applications using automation, you can control the Access application with other applications, such as Excel or a Visual Basic .NET. This means that you can take advantage of Access’s marvelous report writer from your Visual Basic .NET applications. In fact, you can list all the Access reports, allow your user to select one, and then run the report—all from within a Visual Basic .NET Winform.

Defining Some Automation Terms

Before you learn how automation works, you need to understand a few automation terms. Automation requires an automation client and an automation server. The automation client application is the one doing the talking. It is the application that is controlling the server application. Because this book is about Access, most of the examples in this chapter show Access as an automation client, meaning that the Access application is controlling the other application (Excel, Word, and so on). The automation server application is the application being controlled. It contains the objects being manipulated. Excel is acting as an automation server when Access launches Excel, makes it visible, creates a new worksheet, sends the results of a query to the worksheet, and graphs the spreadsheet data. It is Excel’s objects that are being controlled, Excel’s properties that are being changed, and Excel’s methods that are being executed.

Another important component of automation is a type library, which is a dictionary that lists the objects, properties, methods, and events exposed by an automation server application. Type libraries allow the server application’s objects, properties, and methods to be syntax checked by the Access compiler. You can also use a type library to get help on another application’s objects, properties, and methods from within Access.

An object model of an automation server application contains the set of objects that are exposed to automation client applications. The objects within the object model are called object types. When you write automation code, you create and manipulate instances of an object type. These instances are called objects.

Declaring an Object Variable to Reference Your Application

Automation requires that you create object variables that reference application objects. After you create an object variable, you can query and change the object’s properties as well as execute its methods.

You can learn about an object’s properties and methods using its object libraries. An object library contains a listing of all the properties and methods that an object exposes. To be able to view foreign objects from within Access, you must first establish a reference to that application. After a reference is established, you can view that object’s properties and methods using the Object Browser. You can also view any modules and classes that the parent object exposes.

If you want to register an object, the Visual Basic Editor (VBE) must be active. With the Code window active, choose Tools, References. The References dialog box appears, as shown in Figure 24.1.

Figure 24.1. The References dialog box.

Each time you install a program, the Windows Registry is updated. The References dialog box shows you all the objects registered in Windows (see Figure 24.2). If you want to link to one of the available objects from within Access, you must enable the check box to the left of the object name. Then click OK. You can browse that object’s properties and methods in the Object Browser, as shown in Figure 24.3. As covered in Chapter 9, “Objects, Properties, Methods, and Events Explained,” to access the Object Browser, you can choose View, Object Browser, press F2, or click the Object Browser tool while in the Module window. Notice that in Figure 24.3, the Object Browser displays all the classes that belong to the Excel 12.0 object library. The Range class is selected, and all the members of the Range class are displayed in the list box at the right.

Figure 24.2. Registered automation server objects.

Figure 24.3. The Object Browser.

Creating an Automation Object

Before you can talk to an application, you need to know the objects contained within it. You can then use Dim, Private, or Public statements to point to and control various application objects. Each product comes with documentation indicating which objects it supports. You can also view the objects that a product supports by using the Object Browser. After you create an object variable, you can manipulate the object without user intervention.

Declaring an Object Variable

To create an instance of an object, you must first create an object variable that holds a reference to the object. You can do this by using a Dim statement:

Dim objExcel As New Excel.Application

This code creates an object variable pointing to the Excel application object. It then creates a new instance of the Excel application object. This Excel object is part of the Excel application. Visual Basic for Applications (VBA) can control it using the object variable. Unless instructed otherwise, the instance of Excel is invisible. You can make it visible by using this statement:

objExcel.Visible = True

Alternatively, you can use two statements to declare and instantiate an object. The code looks like this:

Dim objExcel as Excel.Application
Set objExcel = New Excel.Application

The Dim statement declares an object variable that is ready to be associated with a running instance of Excel. The Set statement launches Excel and points the object variable at the new instance of Excel. The advantage of this method is that you can better control when the instance of Excel is actually created. If, for example, the declaration is in the General Declarations section of a form, you can place the Set statement under a command button that is used to launch Excel.

Manipulating an Automation Object

After you create an instance of an object, you are ready to set its properties and execute its methods. You can talk to the object through the object variable you created. Using this object variable, you can get and set properties and execute methods.

Setting and Retrieving Properties

All the objects you will be talking to through automation have properties. Properties are the attributes of the object—the adjectives you use to describe the objects. You can use VBA to inquire about the properties of objects and set the values of these properties. Here are some examples:

objExcel.Visible = True
objExcel.Caption = "Hello World"
objExcel.Cells(1, 1).Value = "Here I Am"

Each of these examples sets properties of the Excel application object. The first example sets the Visible property of the object to True. The second example sets the Caption of the object to "Hello World". The final example sets the Value property of the Cells object, contained within the Excel object, to the value "Here I Am".

Executing Methods

Properties refer to the attributes of an object, and methods refer to the actions you take on the object. Methods are the verbs that apply to a particular object type. Here’s an example:

objExcel.Workbooks.Add

This code uses the Add method to add a workbook to the Excel object.

Using Early Binding Versus Late Binding

Binding is another important automation concept. Two types of binding are available with automation components: early binding and late binding. With early binding, you create a reference to a component’s type library. This notifies Access of all the library’s objects, properties, methods, and events. With late binding, you instantiate objects at runtime without referencing them at design time. VBA doesn’t know anything about the objects that you are creating until runtime.

Most objects that you automate support early binding. You should use early binding whenever possible. Early binding has several benefits. Because each object’s properties and methods are resolved at compile time, early binding is faster and more efficient. Furthermore, after you create a reference to a type library, all the library’s objects and their properties and methods are available via IntelliSense. Finally, online help is available for any type libraries that you have referenced. This means, for example, if you have referenced Excel’s library from Access, the process of placing your cursor on an object, property, or method and pressing F1 displays help for the selected item.

Listing 24.1 provides an example of early binding. This code requires that a reference first be made to the Excel object library.

Listing 24.1. An Example of Early Binding

CreateObject and GetObject

CreateObject and GetObject are required when you use late binding. Because, with late binding, Access is not aware of the server application and its objects, properties, methods, and events, you cannot use a Dim statement and a Set statement to declare and instantiate the server application object. Instead, you must use Dim to declare a generic object variable. You then use a Set statement along with the CreateObject or GetObject function to work with the server object. The CreateObject function launches a new instance of the server object. The GetObject function is similar to CreateObject, but it attempts to reference a running instance of the requested application. Furthermore, unlike the CreateObject function that receives only one argument as a parameter, the GetObject function receives an optional parameter with the name of the document you want to work with.

Listing 24.2 provides an example of CreateObject and late binding.

Listing 24.2. Using the CreateObject Function to Create a Late-Bound Instance of Excel

Controlling Excel from Access

Before you attempt to talk to Excel, you must understand its object model. Excel gives you an excellent overview of the Excel object model. You can find this model by searching for “object model” in Excel Help. Each object in the model has hypertext links that enable you to obtain specific help on the object, its properties, and its methods.

When using automation, Excel launches as a hidden window with a Visible property of False. Destroying the Excel object variable does not cause Excel to terminate. To make things even more complicated, each time you use the New keyword within the Dim or Set statement, a new instance of Excel is launched. This means that it is possible for numerous hidden copies of Excel to be running on a user’s machine, which can lead to serious resource problems. If you want to use a running instance of Excel, you can omit the New keyword. This action has its disadvantages as well. Say, for example, that the user of your application has created a large spreadsheet and has not saved it recently. Your application uses an existing instance of Excel, creates a new workbook, prints, and then exits without saving. You might find that your user is very angry about the loss of his important work. For this reason, I have found it preferable to suffer the potential resource costs and create my own instance of Excel. If you want to launch Excel invisibly, do your work, and get out, make sure that you terminate Excel upon completion of your code.

Before you execute code that relies on a running copy of Excel, it is important to ascertain that Excel launched successfully. The function shown in Listing 24.3 attempts to launch Excel. If the launch is successful, True is returned from the function. Otherwise, False is returned from the function.

Listing 24.3. The CreateExcelObj Subroutine

The routine begins by invoking error handling. It initializes the return value for the function to False. The routine then attempts to launch Excel. If it is successful, the public variable gobjExcel references the running instance of Excel, and the function returns True. If an error occurs, the routine executes the code within the error handler. The code displays a message and sets the return value for the function to False.

The CreatExcelObj function is called from the Click event of the cmdFillExcel command button on the frmSimpleExcel form. The application attempts to talk to the Excel object only if the return value of the function is True, indicating that Excel was loaded successfully.

If Excel launches successfully, the FillCells subroutine executes, as shown in Listing 24.4.

Listing 24.4. The FillCells Subroutine

You can find this relatively simple routine in the frmSimpleExcel form, which is part of the CHAP24EX.ACCDB database file (see Figure 24.4). It begins by using the Add method on the Workbooks collection of the Excel object to add a new workbook to the instance of Excel. It then uses Set objWS = gobjExcel.ActiveSheet to provide a shortcut for talking to the active sheet in the new Excel workbook. Using the objWS object reference, it modifies the values of several cells. It then uses the AutoFill method to quickly fill a range of cells with data. It returns the cursor to cell A1, and the Excel object is made visible. You might wonder what the AutoFill method is; it automates the process of filling a range of cells with a pattern of data. Figure 24.5 shows the results. I mention this method here not just to tell you what it is, but also to illustrate an important point: You must know the product you are automating and its capabilities. If you are not familiar with the product from a user’s perspective, you will find it extremely difficult to work with the product using automation.

Figure 24.4. The form used to launch, communicate with, and close Excel.

Figure 24.5. Using the AutoFill method to populate a range of cells.

Closing an Excel Automation Object

After the user clicks the Close Excel command button, the CloseExcel subroutine is called, as shown in Listing 24.5. The subroutine first checks to see whether the gobjExcel object variable is still set. If it is, Excel is still running. The DisplayAlerts property of the Excel application object is set to False. This setting ensures that, when the Quit method is executed, Excel will not warn about any unsaved worksheets. This methodology is acceptable because all work was accomplished using a new instance of the Excel application object. If you want to save your work, you should execute the required code before the Quit method is executed.

Listing 24.5. The CloseExcel Subroutine

Creating a Graph from Access

Now that you have learned how to talk to Excel, you are ready to learn how to do something a bit more practical. Figure 24.6 shows a form called frmCreateExcelGraph. The form shows the result of a query that groups the result of price multiplied by quantity for each country. The Create Excel Graph command button sends the result of the query to Excel and produces the graph shown in Figure 24.7. (Listing 24.6 shows the code that produces this graph.)

Figure 24.6. The form used to create an Excel graph.

Figure 24.7. The result of a query graphed in Excel.

Listing 24.6. Creating a Graph from Access

This routine begins by creating several object variables. It then creates two recordsets and sets the ActiveConnection property of each recordset to the connection associated with the current project. It calls a user-defined function called CreateRecordset, located in the basUtils module. The CreateRecordset function receives three parameters: the two recordset object variables and the name of a query. Listing 24.7 shows the CreateRecordset function.

Listing 24.7. The CreateRecordset Function

The CreateRecordset function begins by counting how many records are returned by the query name that is passed. If the number of records exceeds 500, the function returns False; otherwise, the function opens a recordset based on the query name that is passed and returns True. This function ensures that only a reasonable number of records are sent to Excel and that a recordset can be opened successfully.

If the CreateRecordset function returns True, the remainder of the code in the Click event of the cmdCreateGraph command button executes. The routine uses the CreateExcelObj function to launch Excel. If Excel is opened successfully, the code creates a new workbook. The routine then loops through each field in the Fields collection of the recordset (the result of the query). The values of the cells in the first row of the worksheet are set equal to the names of the fields in the recordset. Next, the routine uses the CopyFromRecordset method of the Excel Range object to copy the contents of the recordset rstData to cell A2 in the active worksheet. The data from each row is placed in a different row within the spreadsheet. The data from each column in a particular row is placed in the various columns of the worksheet. OLE object fields (adLongVarBinary) are excluded from the process.

After all the data in the recordset is sent to Excel, the routine is ready to create a chart. It moves the cursor to cell A1 and then selects the entire contiguous range of data. It adds a chart object to the worksheet and then uses the Chart Wizard to create a chart. Finally, Excel is made visible so that users can see the fruits of their efforts.

Controlling Word from Access

As you discovered in the preceding section, Excel exposes many objects. You can manipulate each of these objects separately, using Excel’s own properties and methods. Prior to Office 97, this was not true for Word, because Word exposed only one object, called Word.Basic. Microsoft Word 97, and versions subsequent to it, all sport the Visual Basic for Applications language. These newer versions of Word expose many objects, just as Excel and other Microsoft products do.

Just as with Excel, you can use the Dim statement or Dim as New statement to launch Word. Like Excel, Word launches as a hidden object. The Word application object has a Visible property, which makes the Word object visible. If you create a Word object using automation, Word will not automatically terminate, even if the object variable is destroyed.

Using Word to Generate a Mass Mailing

Figure 24.8 shows the form called frmMergeToWord, which shows the results of running a query called qryMailMerge. After the user clicks the Merge to Word command button, all the records displayed are sent to a Word mail merge and printed. Figure 24.9 shows an example of the resulting document, and Listing 24.8 shows the code that generated this document.

Figure 24.8. The data that will be merged to Word.

Figure 24.9. The result of the mail merge.

Listing 24.8. Generating a Word Mail Merge Document

The code begins by presenting an hourglass mouse pointer to the user. This helps to ensure that, if the process takes a while, the user knows that something is happening. It then calls the CreateWordObj routine to create a Word object. The CreateWordObj routine is similar to the CreateExcel routine shown earlier in the chapter. The code executes the Open method on the Documents collection of the Word object. It opens a document called customerletter in the current folder. The customerletter document already has been set up to do a mail merge. You will need to create an .odc file (from within Word) pointing at the Customers table. The subroutine sets the Destination property of the MailMerge object to a new document. It sets the SuppressBlankLines property to True. Next, it uses the OpenDataSource method to open the datasource indicated in the .odc file. It then executes the mail merge with the Execute method. This merges the contents of the Customers table with the document and creates a new document with the mail-merged letters. The PrintPreview method is executed on the ActiveDocument object so that the merged document is printed. Finally, the Visible property of the Word object is set to True, making Word visible, and the hourglass vanishes.

Using Word to Overcome the Limitations of Access as a Report Writer

Although in most ways Access is a phenomenal report writer, it does have its limitations. For example, you cannot bold or italicize an individual word or phrase within a text box. This is quite limiting if you need to emphasize something such as a past due amount in a dunning letter. When the document I need to produce appears more like a letter than a report, I often think of Microsoft Word. The document pictured in Figure 24.10 produces a letter that provides information to the recipient of an order. The code shown in Listing 24.9 produces the letter based on the information supplied in frmSendConfirmation.

Figure 24.10. Order confirmation letter produced in Microsoft Word.

Listing 24.9. Working with Word Bookmarks

The example first launches Word. It then gets a reference to a new document based on the Order.dot template. After that, it populates bookmarks in the document with values from the currently displayed order.

Controlling PowerPoint from Access

Believe it or not, you can even control PowerPoint using automation. You can create a presentation, print a presentation, or even run a slide show directly from Access.

PowerPoint launches as a hidden window. To make PowerPoint visible, you must set the Visible property of AppWindow to True. Destroying the PowerPoint object variable does not terminate the PowerPoint application.

The code shown in Listing 24.10 is located under the Click event of the cmdChangePicture command button on the frmOLEToPowerPoint form, which is shown in Figure 24.11. Figure 24.12 shows the resulting PowerPoint slide.

Figure 24.11. The form used to create a PowerPoint slide.

Figure 24.12. A PowerPoint slide created using automation.

Listing 24.10. Using Select Picture

The code in the Click event of cmdChangePicture invokes the File Open common dialog box so that the user can select a picture to be added to the slide. The Filename property returned from this dialog box is used as the SourceDoc property for the automation object. The new picture is then linked to the automation object.

Listing 24.11 shows the routine that creates the PowerPoint slide.

Listing 24.11. Creating the PowerPoint Slide

The routine begins by creating an instance of PowerPoint. The code makes the instance visible. It adds a presentation to the PowerPoint object and then adds a slide to the presentation. The code modifies the background fill of the slide. It then customizes the text, color, and italic properties of the title object. Finally, it uses the SourceDoc property of the olePicture object to create an automation object, which it adds to the slide.

Automating Outlook from Access

Microsoft Outlook is a powerful email client. It is also an excellent tool for both task and contact management. As an application developer, I find many opportunities to automate Outlook from the Access applications that I build. For example, suppose one of my clients sends mass email mailings to selected groups of her customers. I use an Access front end to manipulate customers stored in a SQL Server back end. Included in the front end is a feature that enables the users to generate an email message and then enter the criteria that designates which clients receive the email message. This is one of many examples of how you can integrate the rich features of Access and Outlook.

The form pictured in Figure 24.13 allows the user to select an email template used for a mass mailing. The mailing is sent to all users who meet the criteria entered in a query called qryBulkMail. A more sophisticated example would allow the users to build the query on the fly, using a custom query-by-form. The code that allows the user to select an Outlook email template appears in Listing 24.12.

Figure 24.13. This form allows the user to select the email template used for a mass mailing.

Listing 24.12. Selecting the Outlook Template

The code first sets the filter of the Common Dialog control to show only files with the .oft extension. It then displays the Open dialog box. After the user selects a file, the name and path of the file are placed in the txtTemplate text box. The code required to send the mailing is shown in Listing 24.13.

Listing 24.13. Sending the Outlook Message to the Recipients in the qryBulkMail Resultset

First, the code creates a recordset based on qryBulkMail. It then loops through the recordset. As it visits each row in the resultset, it creates an Outlook message based on the designated template. It adds the email address of the current row as a recipient of the email message. It then either saves the message as a draft or immediately sends it to the designated recipient.

Controlling Access from Other Applications

Many times, you will want to control Access from another application. You might want to run an Access report from a Visual Basic or Excel application, for example. Just as you can tap into many of the rich features of other products (such as Excel) from within Access, you can use some of Access’s features from within another program. Fortunately, it is extremely easy to control Access from within other applications.

You can find an overview of the Access object model in Access Help. Unless you are very familiar with the Access object model, you should look at this graphical representation of Access’s object model before you attempt to use automation to control Access. Access launches with its Visible property set to False. You can change the Visible property of the application object to True to make Access visible.

The form shown in Figure 24.14 is a UserForm associated with an Excel spreadsheet. It is called frmReportSelect and is part of the Excel spreadsheet called RunAccessReports.xls, included on the sample code website. The form enables you to select any Access database. It displays a list of all reports in the selected database; you can use this list to preview an Access report or print multiple Access reports.

Figure 24.14. The UserForm that enables you to print Access reports.

Listing 24.14 shows how this UserForm form accomplishes its work.

Listing 24.14. Creating a Visual Basic Form to Print Reports

The cmdSelectDatabase_Click event routine sets the Filter property of the Common Dialog control to Access database files. The ShowOpen method of the common dialog control is used to display the File Open dialog box to the user. The ListReports routine executes after the user selects a file from the dialog box.

The ListReports subprocedure begins by creating an instance of the Access application. It uses the OpenCurrentDatabase method of the Access object to open the Access database selected by the user in the File Open common dialog box. It then loops through the AllReports collection of the CurrentProject object that is associated with the selected database. It adds the name of each report to the list box.

The routine in Listing 24.15 prints the selected reports.

Listing 24.15. Creating a New Instance of the Access Application Object

The cmdPrint_Click event routine begins by evaluating whether the user selected the print or preview option button. It makes the Access application object visible. The code then loops through the lstReports list box, printing or previewing each selected report. The OpenReport method is used along with the constant acViewPreview or the constant acViewNormal to accomplish this task.

Practical Examples: Using Automation to Extend the Functionality of Your Applications

Many potential applications of automation exist for your applications. One of them is discussed in this section.

The form in Figure 24.15 enables users to select a table or query to send to Excel. The form is called frmSendToExcel.

Figure 24.15. Exporting a table or query to send to Excel.

The Load event of the form is used to add all the table and query names to the list box. The Load event is shown in Listing 24.16. Notice that the function uses the AllTables and AllQueries collections of the current database to populate the list box, excluding all the system tables.

Listing 24.16. Adding Table and Query Names to the List Box

The Click event of the cmdSendToExcel command button sends the selected table or query to Excel. Listing 24.17 shows this code.

Listing 24.17. Sending a Table or Query to Excel

The routine begins by creating a recordset object using the CreateRecordSet function shown in Listing 24.18. It then attempts to launch Excel. If it is successful, it loops through the Fields collection of the recordset resulting from the selected table or query. It lists all the field names as column headings in Excel. Next, it uses the CopyFromRecordset method of the range object to copy all the field values to the rows in the Excel worksheet. Finally, it issues the AutoFilter method so that the user easily can manipulate the data in Excel, filtering it as necessary (see Figure 24.16).

Figure 24.16. Using AutoFilter to analyze data sent to Excel.

Listing 24.18. Checking Recordset Size

This routine, found in basUtils, ensures that the recordset is not too large to send to Excel. If the size of the recordset is acceptable, it creates the recordset and returns True.

Summary

Automation enables you to control other applications from your Access application, and it enables other programs to control your Access application. This chapter began by providing an overview of automation and why you might want to use it. It discussed creating an object variable to reference the application you are automating. After the ins and outs of the object variable were explained, you saw numerous examples of manipulating automation objects. You looked at detailed code showing automation involving Excel, Word, Outlook, and PowerPoint. Finally, you learned about controlling Access from other applications.

The capability to communicate with other applications has become a prerequisite for successful software development. It is extremely important to be aware of the wealth of tools available. The capability to call on other applications’ features is helping to make the world document-centric, rather than application-centric. This means that users can focus on their tasks and not on how they are accomplishing those tasks. Although automation requires significant hardware and also is rather slow, the benefits it provides are often well worth the price.