Application.Wait won't compile 1

[alexyo7]

I've read almost every post about waiting for a shell command to complete before continuing. Most of them use the "application.wait" command in some fashion, but I can't get that line to compile: METHOD OR DATA MEMBER NOT FOUND.

Am I missing a specific reference? The code accepts the application.filesearch command okay.

I noticed that someone else asked this exact same question in 2002 (thread702-292561) but got steered in another direction without ever really getting the question answered.

 

[PHV]

Application.Wait is a method available in Excel VBA, not Access nor word.
You may consider to reference the Windows Script Host Object Model, and the try something like this:
Dim sh As New WshShell, rc As Long
rc = sh.Run("Your shell command", , True)
True means waiting for shell termination with return code in rc.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Hi!

I think the correct answer was given by pjm.

"As far as I know there is no application.wait method" - which goes for Access. There is a wait method in Excel, though.

The sleep API advice given, seems OK, though I haven't used it.

Roy-Vidar

 

[CajunCenturion]

The Wait method only applies to the Excel.Application object. It is NOT a method of the Access.Application object.

The thread that you reference talks about using the Sleep API instead. There are two drawbacks to that approach. First, it puts the entire ProcessID to sleep, so it will also put to sleep any and all processes running within your application. Secondly, Sleep is for a fixed amount of time and is in no way tied to the time required for the shelled process to complete its task. You may not sleep long enough, or may sleep for too long.

There are two ways, one simple but with risk, and one a little more complicated to wait the correct amount of time for a shelled process to continue.

The simple way is to include a reference to the Windows Host Scripting Object, which can then be similar to the following:

Dim RetVal As Long
Dim WSH As IWshShell
Dim WaitForTermination as Boolean
Dim CommandLine as String

Set WSH = New IWshShell_Class
CommandLine = "notepad.exe"
WaitForTermination = True
RetVal = WSH.Run(CommandLine, vbNormalFocus, WaitForTermination)
Set WSH = Nothing

The risk to this approach is that if Scripting has been disabled, then you may run into problems.

The other approach revolves around the WaitForSingleObject API. Somewhat more involved, this is what it looks like. First, you must declare a few APIs and constants, and then your code:

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessID As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Private Const INFINITE = &HFFFF
Private Const SYNCHRONIZE = &H100000
Private Const WAIT_TIMEOUT = &H102

At the appropriate time, Shell out to the program by calling a ShellAndWait routine

ShellAndWait "notepad", vbNormalFocus, True, 100

And of course, you write the ShellAndWait routine, which looks something like this:

Public Sub ShellAndWait(rStr_CmdLine As String, rInt_WindowStyle As Integer, rBol_UseTimeout As Boolean, rLng_MilliSecWait As Long)

   Dim lLng_ProcessID      As Long
   Dim lLng_ProcessHandle  As Long
   
   lLng_ProcessID = Shell(rStr_CmdLine, rInt_WindowStyle)
   If (lLng_ProcessID > 0) Then
      lLng_ProcessHandle = OpenProcess(SYNCHRONIZE, False, lLng_ProcessID)
      If (lLng_ProcessHandle > 0) Then
         If (rBol_UseTimeout =True) Then
            Do While WaitForSingleObject(lLng_ProcessHandle, rLng_MilliSecWait) = WAIT_TIMEOUT
               DoEvents
            Loop
         Else
            WaitForSingleObject lLng_ProcessHandle, INFINITE
         End If
         CloseHandle hProcess
      End If
   End If

End Sub

The use of the timeout feature allows you to abort pre-maturely and return control to your application, should the need arise, such as would be the case if the shelled program got into an infinite loop or such. You would need to put whatever logic to detect your final timeout inside of the innermost Do While loop. In this example, no such logic exists.



Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

We seemed to all be posting at the same time, but do you think we the message accross of where the .Wait method is?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Didn't paste over enough code. The line
CloseHandle hProcess
should read
CloseHandle lLng_ProcessHandle

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[alexyo7]

PHV and CajunCenturion: Thank you for the sample code and the quick response. I was able to get both "short" examples to work.

Alex