Accessing file after program run from Shell completes

Hello. I am running the following script in a excel:

Shell ("mprog.exe outfile.txt")
 
On Error Resume Next
Do Until Len(Dir("junk.tmp")) > 0
FileCopy "outfile.txt", "junk.tmp"
DoEvents
Loop
 
Kill "junk.tmp"
 
On Error GoTo 0
 
Open "outfile.txt" For Input As #1

The idea is to run an external program, which produces an output file which excel then opens and reads. I was having a problem that excel was trying to access the file while it was still being closed by the shell'd program, hence the delay loop (as per: http://support.microsoft.com/kb/147392). By waiting until teh file could be copied, the program is waiting until it is written and closed. The problem is that it still does not work. I get a "Permission Denied" error when I try to open the file. Any suggestions?

Re: shell problem with excel macro

yes, I think you are right that it needs time. The problem is 1) the program takes a long time to open and close, so it's hard to predict how much time it needs 2) the amount of time it will need varies depending on several things and 3) I call this routine several times within the program, so delaying more than the minimum will really slow things down. I would like the program to run as fast as possible without unnecessary or unreasonable delays. That is why I was doing it this way.

Re: Accessing file after program run from Shell completes

Hi Rob-
That was a good idea, although, for some reason, I was still getting hung in the Do loop. In teh meantime, I found some code that does work, though:

Sub LoopUntilFileIsAvailable()
Dim f As Integer
f = FreeFile
On Error Resume Next
Do
Open "c:\test\test.txt" For Input Lock Read As f
Loop Until FreeFile <> f
On Error GoTo 0
Close f
End Sub

This, with some minor mods seems to do the trick. Thank you very much for the help!