how to get vba to wait until a shell command has completed

[ckxplus]

I'm using a batch file "catff.bat" to concatenate a bunch of files.
Catff.bat is called from Word using the shell command, then the
concancatenated file is opened in Word and formatted.The problem is
that the Word macro tries to open the file before the batch file is
finished.

I *did* find a solution to this problem but unfortunately it doesn't
work. There a knowledge base article on this subject at
http://support.microsoft.com/kb/212547. You test whether the task
called from shell exists and wait until it no longer exists. Here's the
example given:

Sub IsTaskRunning()
' If file does not exist, GoTo error trap.
On Error GoTo itrErrTrap
' Run sample batch file in MS-DOS window.
Shell "c:\test.bat"
Do
' Note: When specifying the Application name
' with the Exists(Name) argument, omit the
' Application extension.
test = Tasks.Exists("test")
Loop Until test = 0
MsgBox "The application has quit."
itrErrTrap: ' Note: This line must be left aligned.
If Err > 0 then MsgBox Err.Description
End Sub

I found that if I'd opened the batch file in a text editor then the
macro will go into an infinite loop. Same thing if I've opened a log
file "catff.log". But otherwise "test = Tasks.Exists("catff")" is
always false. The loop is bypassed and the same errors occur.

Part of the problem could be that I can't seem to run
shell("[path]\catff"). The only way I can get it to work is to use
shell("cmd /c "[path]\catff"") (the quotes actually get quite
complicated here). But I've also tried Tasks.Exists("cmd"), that has no
effect. Unless I've got a command shell open, then the macro hangs.
Usually I run shell with the second argument vbHide, other settings
have no effect. Using "cmd /k" also has no effect.

Does anyone know of a way to get this macro example to work? Or an
alternative method to make VBA wait until a shell command has
completed? I'm using Word 2000 under Win2000 BTW, almost forgot that.

Advance thanks,
John Hendrickx

 

[Brian]

Hello John,

Helmut Weber provided a solution to this early in the year. The code he
provided was:

Option Explicit
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As VbAppWinStyle = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'Declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'Get process-handle:
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'wait for process end:
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'clean up:
CloseHandle ProcHnd

End Function
Sub testdunno()
Dim x As Long
x = ShellX("cmd /c dir c:\ / s > c:\test\output.txt")
Beep ' done
End Sub

Hope this helps. Please post back with the result.


--
Brian McCaffery

I'm using a batch file "catff.bat" to concatenate a bunch of files.
Catff.bat is called from Word using the shell command, then the
concancatenated file is opened in Word and formatted.The problem is
that the Word macro tries to open the file before the batch file is
finished.

I *did* find a solution to this problem but unfortunately it doesn't
work. There a knowledge base article on this subject at
http://support.microsoft.com/kb/212547. You test whether the task
called from shell exists and wait until it no longer exists. Here's the
example given:

Sub IsTaskRunning()
' If file does not exist, GoTo error trap.
On Error GoTo itrErrTrap
' Run sample batch file in MS-DOS window.
Shell "c:\test.bat"
Do
' Note: When specifying the Application name
' with the Exists(Name) argument, omit the
' Application extension.
test = Tasks.Exists("test")
Loop Until test = 0
MsgBox "The application has quit."
itrErrTrap: ' Note: This line must be left aligned.
If Err > 0 then MsgBox Err.Description
End Sub

I found that if I'd opened the batch file in a text editor then the
macro will go into an infinite loop. Same thing if I've opened a log
file "catff.log". But otherwise "test = Tasks.Exists("catff")" is
always false. The loop is bypassed and the same errors occur.

Part of the problem could be that I can't seem to run
shell("[path]\catff"). The only way I can get it to work is to use
shell("cmd /c "[path]\catff"") (the quotes actually get quite
complicated here). But I've also tried Tasks.Exists("cmd"), that has no
effect. Unless I've got a command shell open, then the macro hangs.
Usually I run shell with the second argument vbHide, other settings
have no effect. Using "cmd /k" also has no effect.

Does anyone know of a way to get this macro example to work? Or an
alternative method to make VBA wait until a shell command has
completed? I'm using Word 2000 under Win2000 BTW, almost forgot that.

Advance thanks,
John Hendrickx

 

[Helmut Weber]

Hi everybody,

for further information, see also:


http://vb.mvps.org/samples/project.asp?id=Shell32

 

[ckxplus]

Thanks, Brian, Helmut,

Helmut's ShellX macro does the trick, not sure why though. But at least
it works!

Best,
John Hendrickx