Best way to wait

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


.

Relevant Pages

  • Re: ShellExecuteEx "Batch file on SD Card" failing: CE 5
    ... SD Card) using ShellExecuteEx on a CE 5 platform? ... attempts to launch a Batch file that resides in that directory. ... Console Window opens for a very short period and this error message ... SHELLEXECUTEINFO sei =; ...
    (microsoft.public.windowsce.platbuilder)
  • Re: How to follow link from command line
    ... The example above will open the desktop folder from a command window if you ... type in the name of the batch file, that batch file is in the same folder, ... but it opens up an Explorer window. ... supported on Windows unless you use something like ...
    (microsoft.public.windowsxp.general)
  • Re: Remote Desktop Connection to Terminal Services 2k3
    ... I tried the batch file and it worked. ... >>> Once Microsoft Access is opened I then have to open an Access ... >>> database file through the open dialog box from within the ... >>> Microsoft Access opens. ...
    (microsoft.public.windows.terminal_services)
  • Re: Need a batch file or something to autostart LIVE encoding after windows boot up<===CORRECTION
    ... >I can see that the batch file is going to require the session setup ... >the encoder loads and activates the camera. ... >Use a batch file to call to open the same wme file and it just opens ... >>> sure I can put the encoder in the start up folder but that only opens ...
    (microsoft.public.windowsmedia.encoder)
  • RE: After update
    ... your calling routine that there is a problem and not to open the next form. ... Setting the Cancel = True in the second routine will not achieve anything as ... > Exit Sub ... What opens the form you are concerned about, ...
    (microsoft.public.access.formscoding)