Excel 2013 Power Programming with VBA

Chapter 18: Interacting with Other Applications

In This Chapter

• Starting or activating another application from Excel

• Displaying Windows Control Panel dialog boxes

• Using Automation to control another application

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 18-1.

Figure 18-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. ( ...