Page 475 - Microsoft Office Excel 2003 Programming Inside Out

Excel and Other Office Applications

Activating Another Application

You might be wondering what steps to take if the application you want to use is already open.
Using the Shell function would create a second instance of the program taking up valuable
memory. In most cases, you should activate the program that’s running, rather than creating
a new instance of the program.
Consider the following example, in which the ActivateCalculator procedure uses the
AppActivate statement to activate the application if the application is already running. The
argument for the AppActivate statement is the caption of the application’s title bar. If the
AppActivate statement generates an error, it means the Calculator isn’t running, so the
procedure will start the application.

Sub ActivateCalculator()
AppFile = "Calc.exe"
On Error Resume Next
AppActivate "Calculator"
If Err <> 0 Then
CalcTaskID = Shell(AppFile, vbNormalFocus)
If Err = 0 Then MsgBox "Unable to start the Calculator"
End If Chapter 21
End Sub

Binding

You can write an Excel macro to control the most important component of Microsoft Word,
its automation server. In such a macro, Excel would act as the client application and Word
would act as the server application. You can also write a VB application to control Excel. The
process of one application controlling another is referred to as Automation, formerly OLE
Automation.

Automation allows you to develop complex macros that have the ability to control objects
from a variety of applications. This is an incredibly powerful tool when creating application
files. Automation allows the user to interact with multiple applications, such as Access, and
not even realize the interaction is occurring. Automation allows a single seamless interface
for the end user.
As you are aware, you can use the Insert menu to add a variety of Objects into your work-
book. You can alternatively use Excel VBA to create the same types of objects. When you create
the object, you’ll have full access to the object’s properties and methods available from its
host application. This automation method is preferable for developers, rather than using the
Object command from the Insert menu. When the object is embedded, the user must know
how to use the Automation object’s application. However, when you use VBA to manipulate
the object, you can program the object so that the user can manipulate it by clicking the object.
Before you work with the external object, you must create an instance of the object. This can
be done using one of the two types of binding, early or late. Binding refers to matching the
function calls to the actual code that implements the function.

449
Part 6: Excel and the Outside World: Collaborating Made Easy