Using Automation in Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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.



Dim Program As String

Dim TaskID As Double

On Error Resume Next

Program = "calc.exe"

TaskID = Shell(Program, 1)

If Err <> 0 Then

MsgBox "Can't start " & Program

End If



Figure 23-1 shows the Windows calculator displayed as a result of running this procedure.

Figure 23-1:

The Windows Calculator program.

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:

Public TaskID

Sub StartCalculator2()

Dim Program As String Dim TaskID As Double Program = "calc.exe"

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.

Was this article helpful?

0 0

Post a comment