Recipe 11.5 Run Another Program and Pause Until It's Done

11.5.1 Problem

From within your application, you sometimes need to run another Windows application, or a DOS batch file or utility program that requires some time to do its job. You'd like your Access application to pause until this other program has finished its work. Every time you try it, though, the code starts up the other application but then keeps on going. Is there a way to make Access wait until the other application has completed before moving on?

11.5.2 Solution

The Shell function in VBA (and the ShellExecute function we will mention in the Solution in Recipe 11.7) returns a unique long integer value representing the running task. You can use this value—the instance handle for the running application—to track the state of the application. Given an instance handle, you can use the OpenProcess API function to retrieve the process handle for the process. Armed with that process handle, you can then call the GetExitCodeProcess function continually until it sees that the process has shut down. Because this happens automatically once a DOS application has finished running, you can use this technique to wait until a DOS window has closed before moving on in your application.

The sample form in 11-05.MDB, frmTestWait, allows you to try starting both a DOS application and a Windows application, and wait for either to complete. There's also a button that allows you to start a DOS application but continue the attached code. In each of these cases, the sample code attempts to load the text file C:\ACBTEST.TXT (choosing either of the first two buttons sends the output of CHKDSK to C:\ACBTEST.TXT for you) into a text box on the form once the application you've started finishes its work, as shown in Figure 11-6. (In the case where the code doesn't wait for the other application, of course, there's nothing to load.) Use frmTestWait, try each command button to test the functionality demonstrated in this solution. The first button runs CHKDSK, waits until it has written its output to C:\ACBTEST.TXT, and then loads the text file. The second button runs CHKDSK and immediately loads the text file. The final button, Run Notepad, loads a Windows application, Notepad, and waits until you've closed it before loading the text file.

Figure 11-6. frmTestWait after it has run an application

To use this functionality in your own applications, follow these steps:

Table 11-3. Window display options using Shell

Value	VBA constant	Description
0	vbHide	Hidden
1	vbNormalFocus	Restored to its previous state (neither minimized nor maximized)
2	vbMinimizedFocus	Made visible and minimized
3	vbMaximizedFocus	Made visible and maximized
4	vbNormalNoFocus	Displayed, but doesn't gain the input focus
6	vbMinimizedNoFocus	Minimized (as an icon) when started

For example, to start the Windows calculator maximized, use a statement like this:

acbRunAppWait "CALC.EXE", vbMaximizedFocus
MsgBox "Done with the calculator."

You won't see the message box until you finish with the calculator.

11.5.3 Discussion

The secret to the acbRunAppWait subroutine is its use of the Windows API function GetExitCodeProcess. This function takes as a parameter the process handle of an application, which you can retrieve by calling the OpenProcess API function with the instance handle returned by the call to Shell. GetExitCodeProcess monitors a running process and retrieves that process's exit code. As long as the process continues to run, GetExitCodeProcess returns the value STILL_ACTIVE (defined in basRunApp).

Consider the following code, which checks for the existence of a running application:

Do
   ' Attempt to retrieve the exit code, which will
   ' not exist until the application has quit.
   lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
Loop Until lngExitCode <> STILL_ACTIVE

Though this will almost do what you need, it won't quite succeed. You've left Access running a tight loop, waiting for the new application to finish. Unfortunately, this loop grabs all of Access's clock cycles, looping and waiting for the other application to be done. While this loop is active, Access is effectively dead. All the rest of Windows continues to work perfectly, but Access's only thread of execution is completely tied up. You'll see that Access simply can't update its screen, for example, while you're running Notepad.

The solution, then, is to be a good citizen, allowing Access its processing time. To do this, you must add a DoEvents statement inside the loop. This allows Access to continue working while this code loops, waiting for the application you started to finish. (See the Solution in Recipe 7.4 for more information on DoEvents.) Thus, the acbRunAppWait subroutine looks like this:

Public Sub acbRunAppWait(strCommand As String, intMode As Integer)
   ' Run an application, waiting for its completion
   ' before returning to the caller.

   Dim hInstance As Long
   Dim hProcess As Long
   Dim lngRetval As Long
   Dim lngExitCode As Long

   On Error GoTo acbRunAppWait_Err
   ' Start up the application.
   hInstance = Shell(strCommand, intMode)
   hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or SYNCHRONIZE, _
     True, hInstance)
   Do
      ' Attempt to retrieve the exit code, which will
      ' not exist until the application has quit.
      lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
      DoEvents
   Loop Until lngExitCode <> STILL_ACTIVE
   
acbRunAppWait_Exit:
   Exit Sub

acbRunAppWait_Err:
   Select Case Err.Number
      Case acbcErrFileNotFound
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume acbRunAppWait_Exit
End Sub

To use the Shell command, you must specify an executable file. If you need to run a DOS internal command or redirect the output from a program to a text file, you'll need to load a copy of COMMAND.COM to do your work. In addition, you'll need to use the /C switch, indicating to COMMAND.COM that you just want a temporary instance that should quit when the program you run finishes. For example, to run the CHKDSK.EXE program directly, you could use the following function call (all these examples assume that the necessary programs are available in the DOS PATH):

hInstance = Shell("CHKDSK.EXE", vbMinimizedNoFocus)

To run DIR, on the other hand, you'll need to start COMMAND.COM first:

hInstance = Shell("COMMAND.COM /C DIR C:\*.BAT", vbMinimizedNoFocus)

To redirect the output from a program to a text file, you'll also need to use COMMAND.COM:

hInstance = Shell("COMMAND.COM /C CHKDSK C: > C:\ACBTEST.TXT", _
 vbMinimizedNoFocus)

You may also want to study the FileRead subroutine in the sample form's module, which demonstrates how to open a text file and read its contents directly into a control on a form.