Controlling Other Applications

VBA enables you to control other Office applications—in fact, any application that uses VBA as a macro language, or any application that has been designed to make its internal functions available to other programs (or "exposed"), can be controlled directly via VBA. For example, it's possible (but difficult) to control Outlook 2002 from Word, because Outlook has "exposed" parts of its interface.

What should you call applications that can be controlled by other programs? Microsoft has a long history of terminology changes in this arena. You might hear the terms OLE Automation Server, ActiveX Object, ActiveX Container, or COM Server and they all more or less refer to this type of application. If you hear that an application is "exposed," that means you can manipulate it by using the techniques discussed here.

Starting Excel with a VBA/Word Macro

Say you want to create a VBA/Word macro that starts Excel and places some information in a worksheet. For your entrance into Excel, use the CreateObject() function, as in Listing 41.11.

Listing 41.11. RunExcelFromWord

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
With ExcelSheet.Application
'  Show Excel with the open sheet
    .Visible = True
    .Cells(1, 1) = "Hello, Excel!"
    .ActiveWorkbook.SaveAs "Excel Test.xls"
    .Quit
End With
Set ExcelSheet = Nothing
						

Starting Word with a VBA/Excel Macro

Similarly, if you want to create a VBA/Excel macro that starts Word and places information in a document, try the CreateObject() function shown in Listing 41.12.

Listing 41.12. RunWordFromExcel

Dim objWord As Object
Set objWord = CreateObject("Word.Document")
With objWord.Application
    .Selection.TypeText "Hello, Word!"
    .ActiveDocument.SaveAs "Word Test.doc"
End With
Set objWord = Nothing
						

Starting PowerPoint with a VBA/Word Macro

In the same vein, the VBA/Word program in Listing 41.13 creates and saves a new PowerPoint presentation.

Listing 41.13. RunPowerPointFromWord

Dim objPPT As Object
Set objPPT = CreateObject("PowerPoint.Application")
With objPPT
    .Visible = True
    .Presentations.Add
    .Presentations(1).SaveAs "PowerPoint Test.ppt"
    .Quit
End With
Set objPPT = Nothing
						

Commonalities in Controlling One Application with Another

All the methods for controlling one application with another have several details in common:

• You have to set up a variable of type "Object" and then Set that variable to the application generated by the CreateObject() function.

• The parameter used in CreateObject() follows a precise format. Word.Document creates a new Word document; Excel.Sheet creates a new Excel workbook; PowerPoint.Application merely invokes PowerPoint, without creating a new presen tation.

• When you're finished working with an object, you should set it to Nothing, to free up any lingering allocations, and dislodge any hidden copies of the program that might be running.

In almost all other respects, Word, Excel, and PowerPoint behave entirely differently when controlled via CreateObject():

• Word, for example, becomes visible as soon as you run a CreateObject("Word.Document"). Excel and PowerPoint, on the other hand, run hidden—you can't see them at all—unless you set the .Visible value to True.

• When you leave a procedure that's been manipulating Word via CreateObject(), Word stays visible on the screen, and any documents you have opened remain open. Excel, on the other hand, disappears.

• Most frustrating of all, Word documents that have been created or opened via CreateObject() remain open after the program finishes. In Excel, if the program stops, all the open workbooks disappear. You must save Excel workbooks before exiting or all the changes will be lost.

Office isn't the only "exposed" application. Windows 98 and Windows 2000 include the Windows Scripting Host, which offers a huge library of file-related routines that you can call from any VBA program.