Page 474 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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
Chapter 21
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.

448
Part 6: Excel and the Outside World: Collaborating Made Easy