Shell command not finished

[Jonathan Scott via AccessMonster.com]

I am trying to execute a shell command with the Shell() function. Problem
is, execution continues before the command is complete.

I am trying to grab the output of the command by executing it and
redirecting to a file c:\temp.txt. I then open the file and look for the
contents I want.

However, when the next line after Shell() tries to open the file, it is not
there, and tells me so. I look in explorer, see it there, and have Access
continue, at which point it finds the file and continues as expected.

What's the deal? When Shell() returns, I expect that the temp file is where
it is expected, but it is not. How can I resolve this? Should I just loop
until the open finally succeeds?? Or is there some option I need to give
Shell() or cmd? Better yet, is there a way I can get the output without
resorting to unpredictable temp files?

Jonathan Scott

 

[Van T. Dinh]

Shell command is executed asynchronously with your VBA code.

Try the ShellWait in The Access Web:

http://www.mvps.org/access/api/api0004.htm

 

[Klatuu]

What Van is saying is that the shell function launches an application then
the code continues on the next line. You VBA code does not know what the
other app is doing. One way around this is to put some time delay code in to
give the other app time to complete. However, you can't really trust this,
because you don't know how long it will take. It could take longer if more
apps or processes are running on your machine and yo don't know what the
speed of the target (users's) computer is.

One trick is to wrap the code you use to open the file in a loop. If you
are using somethink like SET rst = Currentdb.OpenRecord("MyTextFile"), then
you will get an error 3078. If you are using another method, you will have
to test for the error number. Then put your open inside a For Next Loop.
One thing to be aware of, however is that maybe something went wrong with the
other app and it will not create the file. So it would be wise to initiate a
counter and only try so many times before you error out. Here is some psuedo
code to give you an idea:

Dim intRepeats as Integer
Dim intErrorReturned
Dim varPauseTime as Variant
dim blnFileOpened as Boolean

intRepeats = 10 ' we will try ten times
Shell() ' This is where you kick off the other app.
On Error Resume Next

blnFileOpened = False
For intRepeats = 1 to 10
'Do your Open Here
intErrorReturned = err.number
Select Case intErrorReturned
Case is = 0
blnFileOpened = True
Exit For ' Open worked
Case is = 3078 ' Open Failed
varPauseTime = Timer + 2 ' Set duration to 2 seconds
Do While Timer < varPauseTime
DoEvents ' Yield to other processes.
Loop
Case Else
'Some other error - Go to your error handler
End Select
On Error ..... 'Reset your error handling
If blnFileOpened Then
'Everything is ok
Else
'We never got the file to open
End If

 

[Jonathan Scott via AccessMonster.com]

Thanks! It works like a charm! I tried the looping to wait for the file to
be created, but the code started getting ugly because essentially the logic
is ugly too.

But I'm curious; it just occurred to me that if I assign Shell()'s return
value to a variable, will that perhaps cause it to wait? Asynchronous
updating of variables is perhaps what happens? Ouch!

Anyways, it works now the way I would like it to, I'm not going to fuss for
now.

Thanks again!
Jonathan Scott