Chapter 20: Interacting with Other Applications


Starting or activating another application from Excel

Displaying Windows Control Panel dialog boxes

Using Automation to control another application

Using SendKeys as a last resort

Starting an Application from Excel

Launching another application from Excel is often useful. For example, you might want to execute another Microsoft Office application or even a DOS batch file from Excel. Or, as an application developer, you may want to make it easy for a user to access the Windows Control Panel to adjust system settings.

Using the VBA Shell function

The VBA Shell function makes launching other programs relatively easy. Following is an example of VBA code that launches the Windows Calculator application.

Sub StartCalc()

Dim Program As String

Dim TaskID As Double

On Error Resume Next

Program = “calc.exe”

TaskID = Shell(Program, 1)

If Err <> 0 Then

MsgBox “Cannot start “ & Program, vbCritical, “Error”

End If

End Sub

You'll probably recognize the application that this procedure launches in Figure 20-1.


FIGURE 20-1: Running the Windows Calculator program from Excel.

The Shell function returns a task identification number for the application specified in the first argument. You can use this number later to activate the task. The second argument for the Shell function determines how the application is displayed. ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.