I have a routine that runs a batch file. This batch file produces a log of
it's activity. I then want to read the contents of this file into a
variable.

I can do all of these things and it works well when I step through the code,
but if I run it, I get an error because either the log file doesn't exist,
or Excel can't open it because it's still open and being written to by the
batch file.

My question is this. I can incorporate a fixed delay in the code, but is
there a better way. I suspect looking to see if the file exists won't work
because the batch creates the file then writes to it, so the VBA could find
the file but still not be able to open it. Is there an easy way to try
opening the file and, if it fails try again in a loop? I think I would also
have to incorporate a backup timer in case something goes wrong with the
logfile creation process.

My thoughts are something along the lines of:

x=Now()
Do
If Now() < x + TimeValue("0:00:00") then
Open file
On error then Loop
Goto Copytext
Else
Goto Subend
Endif
Copytext:
Do while not EOF
---etc
Close file
Subend:

I'm not sure if I've got the logic correct here, but the idea is to try to
open the file. If it fails, continue to try until either the file opens (in
which case copy the text to the variable), or 10 seconds elapses (in which
case end the subroutine).

My current routine is below:

Private Sub RunDep(sTPPath, sReport)
Dim x
Shell (sTPPath & "\Deploy2.bat")
sReport = sReport & Chr(13) & "Deployed "
'===== Needs delay in here =====
Application.Wait(Now + TimeValue("0:00:10"))
Open sTPPath & "\deploy.log" For Input As #1
Do While Not EOF(1)
sReport = sReport & Input(1, #1)
Loop
Close #1
End Sub

--
Ian
--

This is a method is different than the method you are attempting to use (it
doesn't use a loop to perform its waiting operation) and is based on code I
have posted previously to the compiled VB newsgroups over the years. The
call statement from your own code to my ShellAndWait subroutine would look
like this...

ShellAndWait ShellStr, vbHide

where ShellStr would be your current sTPPath & "\Deploy2.bat" argument to
your Shell statement. I would suggest placing my code into a Module
(Insert/Module from VBE's menu bar). The next statement after the above
ShellAndWait subroutine call will not be executed until the batch file has
finished.

'********** START OF MODULE CODE **********
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 Declare Function WaitForSingleObject _
Lib "kernel32" _
(ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

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

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim PID As Long
Dim hProcess As Long
If IsMissing(WindowState) Then WindowState = vbNormalFocus
PID = Shell(PathName, WindowState)
If PID = 0 Then
' Handle Error, Shell Didn't Work
MsgBox "Error executing Shell command!"
Else
hProcess = OpenProcess(SYNCHRONIZE, True, PID)
WaitForSingleObject hProcess, INFINITE
CloseHandle hProcess
End If
End Sub
'********** END OF MODULE CODE **********

Rick


"IanC" wrote in message
...
I have a routine that runs a batch file. This batch file produces a log of
it's activity. I then want to read the contents of this file into a
variable.

I can do all of these things and it works well when I step through the
code, but if I run it, I get an error because either the log file doesn't
exist, or Excel can't open it because it's still open and being written to
by the batch file.

My question is this. I can incorporate a fixed delay in the code, but is
there a better way. I suspect looking to see if the file exists won't work
because the batch creates the file then writes to it, so the VBA could
find the file but still not be able to open it. Is there an easy way to
try opening the file and, if it fails try again in a loop? I think I would
also have to incorporate a backup timer in case something goes wrong with
the logfile creation process.

My thoughts are something along the lines of:

x=Now()
Do
If Now() < x + TimeValue("0:00:00") then
Open file
On error then Loop
Goto Copytext
Else
Goto Subend
Endif
Copytext:
Do while not EOF
---etc
Close file
Subend:

I'm not sure if I've got the logic correct here, but the idea is to try to
open the file. If it fails, continue to try until either the file opens
(in which case copy the text to the variable), or 10 seconds elapses (in
which case end the subroutine).

My current routine is below:

Private Sub RunDep(sTPPath, sReport)
Dim x
Shell (sTPPath & "\Deploy2.bat")
sReport = sReport & Chr(13) & "Deployed "
'===== Needs delay in here =====
Application.Wait(Now + TimeValue("0:00:10"))
Open sTPPath & "\deploy.log" For Input As #1
Do While Not EOF(1)
sReport = sReport & Input(1, #1)
Loop
Close #1
End Sub

--
Ian
--

This seems pretty straight forward - but Im having trouble getting it to
execute.
Should 'Pathname' in your ShellandWait sub be the path to the batch job - ie
Shellstr in your example?

My batch job works when I run it from a DOS window, but it just flashes the
DOS window when I do a regular call statement. The batch job is running 4
fortran programs in succession.
Thanks


"Rick Rothstein (MVP - VB)" wrote:

This is a method is different than the method you are attempting to use (it
doesn't use a loop to perform its waiting operation) and is based on code I
have posted previously to the compiled VB newsgroups over the years. The
call statement from your own code to my ShellAndWait subroutine would look
like this...

ShellAndWait ShellStr, vbHide

where ShellStr would be your current sTPPath & "\Deploy2.bat" argument to
your Shell statement. I would suggest placing my code into a Module
(Insert/Module from VBE's menu bar). The next statement after the above
ShellAndWait subroutine call will not be executed until the batch file has
finished.

'********** START OF MODULE CODE **********
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 Declare Function WaitForSingleObject _
Lib "kernel32" _
(ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

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

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim PID As Long
Dim hProcess As Long
If IsMissing(WindowState) Then WindowState = vbNormalFocus
PID = Shell(PathName, WindowState)
If PID = 0 Then
' Handle Error, Shell Didn't Work
MsgBox "Error executing Shell command!"
Else
hProcess = OpenProcess(SYNCHRONIZE, True, PID)
WaitForSingleObject hProcess, INFINITE
CloseHandle hProcess
End If
End Sub
'********** END OF MODULE CODE **********

Rick


"IanC" wrote in message
...
I have a routine that runs a batch file. This batch file produces a log of
it's activity. I then want to read the contents of this file into a
variable.

I can do all of these things and it works well when I step through the
code, but if I run it, I get an error because either the log file doesn't
exist, or Excel can't open it because it's still open and being written to
by the batch file.

My question is this. I can incorporate a fixed delay in the code, but is
there a better way. I suspect looking to see if the file exists won't work
because the batch creates the file then writes to it, so the VBA could
find the file but still not be able to open it. Is there an easy way to
try opening the file and, if it fails try again in a loop? I think I would
also have to incorporate a backup timer in case something goes wrong with
the logfile creation process.

My thoughts are something along the lines of:

x=Now()
Do
If Now() < x + TimeValue("0:00:00") then
Open file
On error then Loop
Goto Copytext
Else
Goto Subend
Endif
Copytext:
Do while not EOF
---etc
Close file
Subend:

I'm not sure if I've got the logic correct here, but the idea is to try to
open the file. If it fails, continue to try until either the file opens
(in which case copy the text to the variable), or 10 seconds elapses (in
which case end the subroutine).

My current routine is below:

Private Sub RunDep(sTPPath, sReport)
Dim x
Shell (sTPPath & "\Deploy2.bat")
sReport = sReport & Chr(13) & "Deployed "
'===== Needs delay in here =====
Application.Wait(Now + TimeValue("0:00:10"))
Open sTPPath & "\deploy.log" For Input As #1
Do While Not EOF(1)
sReport = sReport & Input(1, #1)
Loop
Close #1
End Sub

--
Ian
--