Using Automation in Excel | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, the Excel macro will control Word's automation server. In such circumstances, Excel is the client application, and Word is the server application. Or you can write a Visual Basic application to control Excel. The process of one application's controlling another is sometimes known as Object Linking and Embedding (OLE), or simply automation.

The concept behind automation is quite appealing. A developer who needs to generate a chart, for example, can just 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. An end user might be working with an Access object and not even realize it.

Note	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 this section, 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 - which accounts for an increasing number of applications.

Working with foreign objects using automation

As you might know, you can use Excel's Insert Text Object command to embed an object, such as a Word document, in a worksheet. In addition, you can create an object and manipulate it with VBA. (This action is the heart of Automation.) When you do so, you usually have full access to the object. For developers, this technique is generally more beneficial than embedding the object in a worksheet. When an object is embedded, the user must know how to use the automation object's application. But, when you use VBA to work with the object, you can program the object so that the user can manipulate it by an action as simple as a button click.

Early versus late binding

Before you can work with an external object, you must create an instance of the object. This can be done in either of two ways: early binding or late binding. Binding refers to matching the function calls written by the programmer to the actual code that implements the function.

EARLY BINDING

To use early binding, create a reference to the object library by choosing the Tools References command in the Visual Basic Editor (VBE), which brings up the dialog box shown in Figure 20-4. Then put a check mark next to the object library you need to reference.

Figure 20-4: Adding a reference to an object library file.

After the reference to the object library is established, you can use the Object Browser, shown in Figure 20-5, to view the object names , methods, and properties. To access the Object Browser, press F2 in the VBE.

Figure 20-5: Use the Object Browser to learn about the objects in a referenced library.

When you use early binding, you must establish a reference to a version-specific object library. For example, you can specify Microsoft Word 10.0 Object Library (for Word 2002), or Microsoft Word 11.0 Object Library (for Word 2003), or Microsoft Word 12.0 Object Library (for Word 2007). Then you use a statement like the following to create the object:

 Dim WordApp As New Word.Application

Using early binding to create the object by setting a reference to the object library usually is more efficient and also often yields better performance. Early binding is an option, however, only if the object that you are controlling has a separate type library or object library file. You also need to ensure that the user of the application actually has a copy of the specific library installed.

Another advantage of early binding is that you can use constants that are defined in the object library. For example, Word (like Excel) contains many predefined constants that you can use in your VBA code. If you use early binding, you can use the constants in your code. If you use late binding, you'll need to use the actual value rather than the constant.

Still another benefit of using early binding is that you can take advantage of the VBE Object Browser and Auto List Members option to make it easier to access properties and methods; this feature doesn't work when you use late binding because the type of the object is known only at runtime.

LATE BINDING

At runtime, you use either the CreateObject function to create the object or the GetObject function to obtain a saved instance of the object. Such an object is declared as a generic Object type, and its object reference is resolved at runtime.

GetObject versus CreateObject

VBA's GetObject and CreateObject functions both return a reference to an object, but they work in different ways.

The CreateObject function creates an interface to a new instance of an application. Use this function when the application is not running. If an instance of the application is already running, a new instance is started. For example, the following statement starts Excel, and the object returned in XLApp is a reference to the Excel.Application object that it created.

 Set XLApp = CreateObject("Excel.Application")

The GetObject function is either used with an application that's already running or to start an application with a file already loaded. The following statement, for example, starts Excel with the file Myfile.xls already loaded. The object returned in XLBook is a reference to the Workbook object (the Myfile.xlsx file):

 Set XLBook = GetObject("C:\Myfile.xlsx")

You can use late binding even when you don't know which version of the application is installed on the user's system. For example, the following code, which works with Word 97 and later, creates a Word object:

 Dim WordApp As Object Set WordApp = CreateObject("Word.Application")

If multiple versions of Word are installed, you can create an object for a specific version. The following statement, for example, uses Word 2003:

 Set WordApp = CreateObject("Word.Application.11")

The Registry key for Word's Automation object and the reference to the Application object in VBA just happen to be the same: Word.Application . They do not, however, refer to the same thing. When you declare an object As Word.Application or As New Word. Application , the term refers to the Application object in the Word library. But when you invoke the function CreateObject("Word.Application") , the term refers to the moniker by which the latest version of Word is known in the Windows System Registry. This isn't the case for all automation objects, although it is true for the main Office 2007 components . If the user replaces Word 2003 with Word 2007, CreateObject("Word. Application") will continue to work properly, referring to the new application. If Word 2007 is removed, however, CreateObject("Word.Application.12") , which uses the alternate version-specific name for Word 2007, will fail to work.

The CreateObject function used on an automation object such as Word.Application or Excel.Application always creates a new instance of that automation object. That is, it starts up a new and separate copy of the automation part of the program. Even if an instance of the automation object is already running, a new instance is started, and then an object of the specified type is created.

To use the current instance or to start the application and have it load a file, use the GetObject function.

Note

If you need to automate an Office application, it is recommended that you use early binding and reference the earliest version of the product that you expect could be installed on your client's system. For example, if you need to be able to automate Word 2000, Word 2002, Word 2003, and Word 2007, you should use the type library for Word 2000 to maintain compatibility with all four versions. This, of course, will mean that you can't use features found only in the later version of Word.

A simple example of late binding

The following example demonstrates how to create a Word object by using late binding. This procedure creates the object, displays the version number, closes the Word application, and then destroys the object (thus freeing the memory that it used):

 Sub GetWordVersion()     Dim WordApp As Object     Set WordApp = CreateObject("Word.Application")     MsgBox WordApp.Version     WordApp.Quit     Set WordApp = Nothing End Sub

Note	The `Word` object that's created in this procedure is invisible. If you'd like to see the object's window while it's being manipulated, set its `Visible` property to `True` , as follows : WordApp.Visible = True

This example can also be programmed using early binding. Before doing so, choose Tools References to set a reference to the Word object library. Then you can use the following code:

 Sub GetWordVersion()     Dim WordApp As New Word.Application     MsgBox WordApp.Version     WordApp.Quit     Set WordApp = Nothing End Sub

Controlling Word from Excel

The example in this section demonstrates Automation by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each document to a file. The information used to create the memos is stored in a worksheet, as shown in Figure 20-6.

Figure 20-6: Word automatically generates three memos based on this Excel data.

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 the action occurs behind the scenes: That is, Word is not visible.

 Sub MakeMemos() '   Creates memos in word using Automation     Dim WordApp As Object     Dim Data As Range, message As String     Dim Records As Integer, i As Integer     Dim Region As String, SalesAmt As String, SalesNum As String     Dim SaveAsName As String '   Start Word and create an object (late binding)     Set WordApp = CreateObject("Word.Application") '   Information from worksheet     Set Data = Sheets("Sheet1").Range("A1")     Message = Sheets("Sheet1").Range("Message") '   Cycle through all records in Sheet1     Records = Application.CountA(Sheets("Sheet1").Range("A:A"))     For i = 1 To Records '       Update status bar progress message         Application.StatusBar = "Processing Record " & i '       Assign current data to variables         Region = Data.Cells(i, 1).Value         SalesNum = Data.Cells(i, 2).Value         SalesAmt = Format(Data.Cells(i, 3).Value, "#,000") '       Determine the filename         SaveAsName = Application.DefaultFilePath & _            "\" & Region & ".docx" '       Send commands to Word         With WordApp             .Documents.Add             With .Selection             .Font.Size = 14             .Font.Bold = True             .ParagraphFormat.Alignment = 1             .TypeText Text:="M E M O R A N D U M"             .TypeParagraph             .TypeParagraph             .Font.Size = 12             .ParagraphFormat.Alignment = 0             .Font.Bold = False             .TypeText Text:="Date:" & vbTab & _                 Format(Date, "mmmm d, yyyy")             .TypeParagraph             .TypeText Text:="To:" & vbTab & Region & _              " Manager"             .TypeParagraph             .TypeText Text:="From:" & vbTab & _                Application.UserName             .TypeParagraph             .TypeParagraph             .TypeText Message             .TypeParagraph             .TypeParagraph             .TypeText Text:="Units Sold:" & vbTab & _              SalesNum             .TypeParagraph             .TypeText Text:="Amount:" & vbTab & _               Format(SalesAmt, "$#,##0")         End With             .ActiveDocument.SaveAs FileName:=SaveAsName     End With Next i '   Kill the object     WordApp.Quit     Set WordApp = Nothing '   Reset status bar     Application.StatusBar = ""     MsgBox Records & " memos were created and saved in " & _       Application.DefaultFilePath End Sub

Figure 20-7 shows one of the documents created by the MakeMemos procedure.

Figure 20-7: An Excel procedure created this Word document.

CD-ROM

This workbook, named make memos.xlsm , is available on the companion CD-ROM.

Creating this macro involved several steps. I started by recording a macro in Word. I recorded my actions while creating a new document, adding and formatting some text, and saving the file. That Word macro provided the information that I needed about the appropriate properties and methods. I then copied the macro to an Excel module. Notice that I used With-End With . I added a dot before each instruction between With and End With . For example, the original Word macro contained (among others) the following instruction:

 Documents.Add

I modified the macro as follows:

 With WordApp     .Documents.Add '   more instructions here End With

The macro that I recorded in Word used a few of Word's built-in constants. Because this example uses late binding, I had to substitute actual values for those constants. I was able to learn the values by using the Immediate window in Word's VBE.

Controlling Excel from another application

You can, of course, 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")

The code that follows is a procedure that is located in a VBA module in a Word 2007 document. This procedure creates an Excel Worksheet object (whose moniker is "Excel. Sheet" ) from an existing workbook and pastes it into the Word file.

 Sub MakeLoanTable()     Dim XLSheet As Object     Dim LoanAmt     Dim Wbook As String '   Prompt for values     LoanAmt = InputBox("Loan Amount?")     If LoanAmt = "" Then Exit Sub '   Clear the document     ThisDocument.Content.Delete '   Create Sheet object     Wbook = ThisDocument.Path & "\mortgagecalcs.xlsx"     Set XLSheet = GetObject(Wbook, "Excel.Sheet").ActiveSheet '   Put values in sheet     XLSheet.Range("LoanAmount") = LoanAmt     XLSheet.Calculate '   Insert page heading     Selection.Style = "Title"     Selection.TypeText "Loan Amount: " & _       Format(LoanAmt, "$#,##0")     Selection.TypeParagraph     Selection.TypeParagraph '   Copy data from sheet & paste to document     XLSheet.Range("DataTable").Copy     Selection.Paste     Selection.TypeParagraph     Selection.TypeParagraph '   Copy chart and paste to document     XLSheet.ChartObjects(1).Copy     Selection.PasteSpecial _         Link:=False, _         DataType:=wdPasteMetafilePicture, _         Placement:=wdInLine '   Kill the object     Set XLSheet = Nothing End Sub

CD	This example is available on the companion CD-ROM. The Word document is named `automate excel.docm` , and the Excel workbook is named `mortgagecalcs.xlsx` . When you open the Word file, execute the MakeLoanTable macro by choosing Insert Mortgage Get Mortgage Amount.

The Excel worksheet used by this Word procedure is shown in Figure 20-8. The MakeLoanTable procedure prompts the user for a loan amount and inserts the value into cell C7 (named LoanAmount ).

Figure 20-8: A VBA procedure in Word uses this worksheet.

Recalculating the worksheet updates a data table in range F2:I12 (named DataTable ), and also updates the chart. The DataTable range and the chart are then copied from the Excel object and pasted into the Word document. The result is shown in Figure 20-9.

Figure 20-9: The Word VBA procedure uses Excel to create this document.