Starting an Application from Excel

Starting up 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 might want to make it easy for a user to access the Windows Control Panel.

Using the VBA Shell function

The VBA Shell function makes launching other programs relatively easy. The StartCalc procedure that follows 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. 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 values for this argument.

Displaying a Folder Window

The Shell function is also handy if you need to display a particular directory using Windows Explorer. For example, the statement that follows displays the folder of the active workbook (but only if the workbook has been saved):

 If ActiveWorkbook.Path <> "" Then _   Shell "explorer.exe " & ActiveWorkbook.Path, vbNormalFocus 

 

If the Shell function is not successful, 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.

It's important to understand that your VBA code does not pause while the application that was started with the Shell function is running. In other words, the Shell function runs the application asynchronously. If the procedure has more instructions after the Shell function is executed, they are executed concurrently with the newly loaded program. If any instruction requires user intervention (for example, displaying a message box), Excel's title bar flashes while the other application is active.

In some cases, you might want to launch an application with the Shell function, but you need your VBA code to pause until the application is closed. For example, the launched application might generate a file that is used later in your code. Although you can't pause the execution of your code, you can create a loop that does nothing except monitor the application's status. The example that follows displays a message box when the application launched by the Shell function has ended:

 Declare Function OpenProcess Lib "kernel32" _     (ByVal dwDesiredAccess As Long, _     ByVal bInheritHandle As Long, _     ByVal dwProcessId As Long) As Long Declare Function GetExitCodeProcess Lib "kernel32" _     (ByVal hProcess As Long, _     lpExitCode As Long) As Long Sub StartCalc2()     Dim TaskID As Long     Dim hProc As Long     Dim lExitCode As Long     Dim ACCESS_TYPE As Integer, STILL_ACTIVE As Integer     Dim Program As String     ACCESS_TYPE = &H400     STILL_ACTIVE = &H103     Program = "Calc.exe"     On Error Resume Next '   Shell the task     TaskID = Shell(Program, 1) '   Get the process handle     hProc = OpenProcess(ACCESS_TYPE, False, TaskID)     If Err <> 0 Then         MsgBox "Cannot start " & Program, vbCritical, "Error"         Exit Sub     End If     Do 'Loop continuously '      Check on the process        GetExitCodeProcess hProc, lExitCode '      Allow event processing        DoEvents     Loop While lExitCode = STILL_ACTIVE '   Task is finished, so show message     MsgBox Program & " was closed" End Sub 

While the launched program is running, this procedure continually calls the GetExitCodeProcess function from within a Do-Loop structure, testing for its returned value ( lExitCode ). When the program is finished, lExitCode returns a different value, the loop ends, and the VBA code resumes executing.

CD-ROM  

Both of the preceding examples are available on the companion CD-ROM. The filename is  start calculator.xlsm .

Using the Windows ShellExecute API function

ShellExecute is a Windows Application Programming Interface (API) function that is useful for starting other applications. Importantly, this function can start an application only if an associated filename is known ( assuming that the file type is registered with Windows). For example, you can use ShellExecute to open a Web document by starting the default Web browser. Or you can use an e-mail address to start the default e-mail client.

The API declaration follows (this code goes at the top of a VBA module):

 Private Declare Function ShellExecute Lib "shell32.dll" _   Alias "ShellExecuteA" (ByVal hWnd As Long, _   ByVal lpOperation As String, ByVal lpFile As String, _   ByVal lpParameters As String, ByVal lpDirectory As String, _   ByVal nShowCmd As Long) As Long 

The following procedure demonstrates how to call the ShellExecute function. In this example, it opens a graphics file by using the graphics program that's set up to handle GIF files. If the result returned by the function is less than 32, then an error occurred.

 Sub ShowGraphic()     Dim FileName As String     Dim Result As Long     FileName = ThisWorkbook.Path & "\flower.jpg"     Result = ShellExecute(0&, vbNullString, FileName, _         vbNullString, vbNullString, vbNormalFocus)     If Result < 32 Then MsgBox "Error" End Sub 

The next procedure opens a text file, using the default text file program:

 Sub OpenTextFile()     Dim FileName As String     Dim Result As Long     FileName = ThisWorkbook.Path & "\textfile.txt"     Result = ShellExecute(0&, vbNullString, FileName, _         vbNullString, vbNullString, vbNormalFocus)     If Result < 32 Then MsgBox "Error" End Sub 

The following example is similar, but it opens a Web URL by using the default browser.

 Sub OpenURL()     Dim URL As String     Dim Result As Long     URL = "http://office.microsoft.com"     Result = ShellExecute(0&, vbNullString, URL, _         vbNullString, vbNullString, vbNormalFocus)     If Result < 32 Then MsgBox "Error" End Sub 

This technique can also be used with an e-mail address. The example below opens the default e-mail client and then addresses an e-mail to the recipient.

 Sub StartEmail()     Dim Addr As String     Dim Result As Long     Addr = "mailto:[e-mail]"     Result = ShellExecute(0&, vbNullString, Addr, _         vbNullString, vbNullString, vbNormalFocus)     If Result < 32 Then MsgBox "Error" End Sub 

CD  

These examples are available on the companion CD-ROM in a file named  shellexecute examples.xlsm .