Download the sample database

The Shell function in VBA returns a unique long integer value that represents a running application. 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. Then, by using that process handle, you can call the GetExitCodeProcess function continually until it sees that the process has shut down.

Use the sample form frmTestWait in RunAppAndWait.accdb to experiment. Try starting a command window and a Windows application, and then waiting for either to complete. You can also click a button to start a command window and continue to run the attached code. In each case, the sample code attempts to load the text file C:\WaitTest.txt into a text box on the form once the application that you start finishes its work.

Note:
In the case where the code doesn't wait for the other application, there is nothing to load. Also, choosing either of the first two buttons sends the output of CHKDSK to C:\WaitTest.txt for you.

The first button on frmTestWait runs CHKDSK, waits until it has written its output to C:\WaitTest.txt, and then loads the text file. The second button runs CHKDSK and immediately loads the text file. The last button, Run Notepad, loads the Notepad Windows application and waits until you close it before loading the text file.

Figure 1. Sample form frmTestWait after running the CHKDSK application

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

1. Import the module basRunApp from RunAppWait.accdb into your application.

2. To run another application and wait for it to finish before going on with your code, call the RunAppWait subroutine and pass it two parameters: a command string that tells it what to run, and an integer from the following table that designates which window mode you want to use.

   Table 1. Window Display Options Using Shell

   Value	Control 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 similar to the following.

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

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

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

Consider the following code from the RunAppWait procedure, 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.
    Call GetExitCodeProcess(hProcess, lngExitCode)
    DoEvents
Loop Until lngExitCode <> STILL_ACTIVE

This code watches for the running process to complete. The DoEvents statement in the code is crucial; it leaves Access running a tight loop, waiting for the new application to finish. Unfortunately, while this loop is active, Access is effectively dead; its one thread of execution completely tied up. For example, Access cannot update its screen while you run Notepad. (Comment out the DoEvents code and try running Notepad to verify the behavior.)

The solution is to give Access processing time by adding a DoEvents statement inside the loop. The following version of RunAppWait lets Access continue to work while it is waiting for the secondary application to finish.

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

Const PROCESS_QUERY_INFORMATION = &H400
Const SYNCHRONIZE = &H100000

Const STILL_ACTIVE = &H103&

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

   On Error GoTo HandleError
   ' 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.
      Call GetExitCodeProcess(hProcess, lngExitCode)
      DoEvents
   Loop Until lngExitCode <> STILL_ACTIVE
   
ExitHere:
   Exit Sub

HandleError:
   Select Case Err.Number
      Case errFileNotFound
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume ExitHere
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 must load a copy of the command processor, Cmd.exe, to do your work. In addition, you must use the /C switch to indicate to the command processor 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.

Note:
The examples assume that the necessary programs are available in the DOS PATH.

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

To redirect the output from a program to a text file, call the command processor instead by using code similar to the following.

Private Const FILE_NAME = "C:\WaitTest.txt"
Private Const COMMAND_TEMPLATE = "cmd /C CHKDSK C: > {0}"

Dim command As String
command = Replace(COMMAND_TEMPLATE, "{0}", FILE_NAME)
RunAppWait command, vbHide

To run an application and then return immediately to Access, you can call the simple RunApp procedure in basRunApp, which looks like the following code (as you can see, most of the code is error handling).

Sub RunApp(strCommand As String, intMode As Integer)

   ' Run an application, returning immediately to
   ' the caller. Just presented as a parallel for
   ' RunAppWait.

   On Error GoTo HandleError
   Shell strCommand, intMode
   
ExitHere:
   Exit Sub
   
HandleError:
   Select Case Err.Number
      Case errFileNotFound
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume ExitHere
End Sub

• Access 2007 Samples: Running and Pausing Other Applications from Access

• Access Developer Center

• Access 2007 Resource Center

• Getting the Most Out of Visual Basic for Applications

• OpenProcess Function

• GetExitCodeProcess Function

• Shell Function