Starting Another Application

---

There are times when you need to start another application from Excel. Later in this chapter, you'll review how to interact with other Office applications, but right now you'll review how to open an application that falls outside the Office application scope.

There are a variety of reasons why you might need to interact with an additional application. For example, you might want to use the Calculator, Character Map, or even a DOS batch file from Excel. In fact, you can execute a Control Panel application, if necessary. To accomplish these tasks using VBA code, use the Shell function. The following procedure starts the Calculator application using the Shell function:

Sub RunCalculator()
    On Error Resume Next
    Program = "calc.exe"
    TaskID = Shell(Program, vbNormalFocus)
    If Err <> 0 Then
        MsgBox "Unable to start " & Program, vbCritical, "Error"
    End If
End Sub

The Shell function returns the task identification number for the application. The task identification number can be used in later code to activate the task. The second argument for the Shell function determines the window state of the application, such as minimized, maximized, hidden, or normal. If the function generates an error, the On Error statement will display a message indicating that the file can't be found.

Note 

Remember that if you have subsequent code following the Shell function, the code will be executed immediately. If an instruction requires user intervention, Excel's title bar flashes to notify the user while the other application is active.

An alternate method used to start an application is using the Start command. The Start command can be executed from the Run dialog box or directly from a DOS window. This command is available in most versions of Microsoft Windows. The Start command is used to start a Windows-based application from a DOS window. Using the Start command doesn't require the code to open the program associated with the document; the command uses the program associated with the filename to open the application as well as the file. You are required to enter the full path to the file and extension for this command to execute properly. Figure 21-1 shows an example of how to open the Fall Initiative.ppt file using the Start command.

Figure 21-1: You can use a Start command in the Run dialog box to open a presentation file.

You're able to achieve the same results using the Start command within your VBA code. For example, a sales representative from The Garden Company can launch PowerPoint and open a Marketing presentation file from Excel. Review the code that follows to see how to achieve these results.

Note 

The following example will work if you are using an operating system previous to Microsoft Windows NT. If you have Microsoft Windows NT or later, you will need to use the ShellExecute function to achieve the same results. The ShellExecute function is explained in the next example. Although you need Microsoft Windows 2000 or Windows XP (or later) to run Excel 2003, this procedure will work if you're trying to run Excel 2003 code in an older version of Excel. There might, of course, be other incompatibilities.

Sub OpenPresentation()
    Filename = "C:\Garden Supply Company\Marketing\Fall Initiative.ppt"
    Shell "Start " & Filename
End Sub

Tip 

Automating Mail
To send an e-mail message using VBA code, the Start command is an effective way to start the message. Use Shell "Start [e-mail]' to start the default mail client. If you are using Windows NT or later, you will need to replace the Shell command with the ShellExecute function.

Because the Start command isn't available with Windows NT or later operating systems, you need to use the ShellExecute function to achieve similar results. The following example uses the ShellExecute function to open Microsoft's home page:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hWnd As Long, ByVal lpOperations As String, ByVal lpfile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd _
    As Long) As Long
 
Sub OpenFile()
    File = "http://www.microsoft.com"
    Call ShellExecute(0&, vbNullString, file, vbNullString, vbNullString, _
        vbNormalFocus)
End Sub 

Inside Out
Simulate a Pause in VBA code

There might be times when you are required to launch an application; however, you'll want to pause your VBA code until the application is closed. For example, the application launched is creating a file that is required later in your procedure. Because you're unable to pause your code, you can work around this situation by programming a loop that monitors the applications status. The following procedure was designed to display a message box when the application launched by the Shell function is no longer active:

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 RunCharMap()
    Dim TaskID As Long
    Dim hProc As Long
    Dim lExitCode As Long
    
    ACESS_TYPE = &H400
    STILL_ACTIVE = &H103
    
    Program = "Charmap.exe"
    On Error Resume Next
    
    TaskID = Shell(Program, vbNormalFocus)
    hProc = OpenProcess(ACCESS_TYPE, False, TaskID)
    
    If Err <> 0 Then
        MsgBox "Unable to start " & Program, vbCritical, "Error"
        Exit Sub
    End If
    
    Do
        GetExitCodeProcess hProc, lExitCode
        DoEvents
    Loop While lExitCode = STILL_ACTIVE
    
    MsgBox Program & " is no longer the active application"
End Sub

While the launched program is active, the procedure continues to loop until the lExitCode returns a different value. When the loop ends, the VBA code will resume.

---