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 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
for further information, see also:
http://vb.mvps.org/samples/project.asp?id=Shell32
--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000 (german versions)
Helmut's ShellX macro does the trick, not sure why though. But at least
it works!
Best,
John Hendrickx