I
IanC
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
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