In This Chapter
► Starting or activating another application from Excel
► Controlling Word from Excel and vice versa Sending personalized e-mail from Excel
If you use Excel, you likely use other applications that comprise Microsoft <C Office. Just about everyone uses Word, and you’re probably familiar with PowerPoint or Access.
In this chapter I present some simple examples that demonstrate how to use Excel VBA to interact with other Microsoft Office applications.
Starting Another Application from Excel
Starting another application from Excel is often useful. For example, you might want to launch another Microsoft Office application or even a DOS batch file from an Excel VBA macro.
Using the VBA Shell function
The VBA Shell function makes launching another program relatively easy. The following example starts the Windows Calculator program, which is named CALC.EXE:
This modified procedure uses an AppActivate statement to activate the application (Windows Calculator in this case) if it’s already running. The argument for AppActivate is the Caption of the application’s title bar. If the AppActivate statement generates an error, it means the Calculator isn’t running. If it’s not running, the routine starts the application using the Shell function.
Activating a Microsoft Office application
If the application that you want to start is one of several Microsoft applications, use the Application object’s ActivateMicrosoftApp method. For example, the following procedure starts Word:
If Word is already running when the preceding procedure is executed, it is activated. Other constants are available for this method:
xlMicrosoftPowerPoint (PowerPoint)
xlMicrosoftMail (Outlook)
xlMicrosoftAccess (Access) xlMicrosoftFoxPro (FoxPro) xlMicrosoftProject (Project) xlMicrosoftSchedulePlus (SchedulePlus)
Using Automation in Excel
You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, Excel macros control the most important component of Word: the so-called automation server. In such circumstances, Excel is called the client application, and Word is the server application.
Figure 23-1 shows the Windows calculator displayed as a result of running this procedure.
Figure 23-1:
The Windows Calculator program.
The Shell function returns a task identification number for the application. You can use this number later to activate the task. The second argument for the Shell function determines how the application is displayed. (1 is the code for a normal-size window, with the focus.) Refer to the Help system for other argument values.
If the Shell function is unsuccessful, it generates an error. Therefore, this procedure uses an On Error statement to display a message if the executable file cannot be found or if some other error occurs.
But what if the Calculator program is already running? The StartCalculator procedure simply opens another instance of the program. In most cases, you want to activate the existing instance. The following modified code solves this problem:
The concept behind automation is quite appealing. A developer who needs to generate a chart, for example, can reach into another application’s grab bag of objects, fetch a Chart object, and then manipulate its properties and use its methods. Automation, in a sense, blurs the boundaries between applications. For example, using automation, an end user might be working with an Access object inside Excel and not even realize it.
Some applications, such as Excel, can function as either a client application or a server application. Other applications can function only as client applications or only as server applications.
In the following sections, I demonstrate how to use VBA to access and manipulate the objects exposed by other applications. The examples use Microsoft Word, but the concepts apply to any application that exposes its objects for automation.
Getting Word’s Version number
The following example demonstrates how to create a Word object to provide access to the objects in Word’s object model. This procedure creates the object, displays the version number, closes the Word application, and then destroys the object, freeing up the memory that it used:
The Word object that’s created in this procedure is invisible. If you want to see the object while it’s being manipulated, set its Visible property to True, as follows:
Most of the automation examples in this chapter use late binding as opposed to early binding. What’s the difference? When you use early binding, you must establish a reference to a version-specific object library, using ToolsO References in the VBE. When you use late binding, setting that reference is not required. Both approaches have pros and cons.
Controlling Word from Excel
The example in Figure 23-2 demonstrates an automation session by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each memo to a separate file. The information used to create the memos is stored in a worksheet.
Figure 23-2:
Word automatically generates three memos based on this Excel data.
The code for the MakeMemos procedure is too lengthy to list here, but you can go to this topic’s Web site to check it out.
The MakeMemos procedure starts by creating an object called WordApp. The routine cycles through the three rows of data in Sheet1 and uses Word’s properties and methods to create each memo and save it to disk. A range named Message (in cell E6) contains the text used in the memo. All of the action occurs behind the scenes: Word is not visible. Figure 23-3 shows a document created by the MakeMemos procedure.
Controlling Excel from Word
As you might expect, you can also control Excel from another application (such as another programming language or a Word VBA procedure). For example, you might want to perform some calculations in Excel and return the result to a Word document.
You can create any of the following Excel objects with the adjacent functions:
Application object: CreateObject(“Excel.Application”) Workbook object: CreateObject(“Excel.Sheet”) Chart object: CreateObject(“Excel.Chart”)
Figure 23-3:
An Excel VBA procedure created this Word document.
The example described in this section is a Word macro that creates an Excel Workbook object (whose moniker is Excel.Sheet) from an existing workbook named projections.xls. The macro prompts the user for two values and then creates a data table and chart, which are stored in the Word document.
The initial workbook is shown in Figure 23-4. The Make ExcelChart procedure prompts the user for two values and inserts the values into the worksheet.
Figure 23-4:
A VBA procedure in Word uses this worksheet.
Recalculating the worksheet updates a chart. The data and the chart are then copied from the Excel object and pasted into a new document. The results are shown in Figure 23-5.
Figure 23-5:
The Word VBA procedure uses Excel to create this document.
The code for the MakeExcelChart procedure follows:
This example is available at the topic’s Web site.
Sending Personalized E-mail Using Outlook
The example in this section demonstrates automation with Microsoft Outlook. The code creates personalized e-mail messages by using data stored in an Excel worksheet.
Figure 23-6 shows a worksheet that contains data used in e-mail messages: name, e-mail address, and bonus amount. This procedure loops through the rows in the worksheet, retrieves the data, and creates an individualized message (stored in the Msg variable).
Figure 23-6:
This information is used in the Outlook Express e-mail messages.
This example uses the Display method, which simply displays the email messages. To actually send the messages, use the Send method instead.
Notice that two objects are involved: Outlook and MailItem. The Outlook object is created with this statement:
The MailItem object is created with this statement:
The code sets the To, Subject, and Body properties, and then uses the Send method to send each message. Figure 23-7 shows one of the e-mails created by Excel.
Figure 23-7:
Create a personalized e-mail by using Excel.
This example is available on this topic’s Web site. To use this example you must have Microsoft Outlook installed.
Working with ADO
ActiveX Data Objects (ADO) is an object model that enables you to access data stored in a variety of database formats. This allows you to use a single object model for all your databases. In this section I present a simple example that uses ADO to retrieve data from an Access database.
ADO programming is a very complex topic. If you need to access external data in your Excel application, invest in one or more topics that cover this topic in detail. This example is here so you can get a feel for how it works.
The following example retrieves data from an Access database named budget.mdb. This database contains one table named Budget, which has seven fields. This example retrieves the data in which the Item field contains the text “Lease” and the Division field contains the text “N. America.” The qualifying data is stored in a Recordset object, and the data is then transferred to a worksheet (see Figure 23-8).
Figure 23-8:
Retrieve data from an Access database.
Unlike the other examples in this chapter, this procedure uses early binding. Therefore, it requires a reference to the Microsoft ActiveX Data Objects 2.0 Library. In the VBE, use Tools References to create this reference.
This example, along with the Access database, is available from this topic’s Web site.
