Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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.

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:bgates@microsoft.com" Result = ShellExecute(0&, vbNullString, Addr, _ vbNullString, vbNullString, vbNormalFocus) If Result < 32 Then MsgBox "Error" End Sub