Using Shell - Why it does not work for me?

[actjfc]

Dear friends,

I have this VB code in a macro:

Sub BuildPRNFiles()
Dim RetVal

Start = Range("c5")
Finish = Range("c6")
'D9 contains the PATH

For x = Start To Finish

RetVal = Shell(Range("D9") & "r.bat" & " " & x, 1)

Delay = 3000
For y = 1 To Delay
Next y

Next x

End Sub

r.bat is a bacth file that runs a very old DOS program. r.bat runs just fine if I run it one at a time in DOS. However, if I want ot run it 1000 times using shell in Excel, it does not work. Excel is faster than this old DOS program. I tried the "delay" approach but it did not work always. The DOS program runs in a few seconds, but the delay works only sometimes not all times. So, excel runs the next one before the previous one has finished.

My question: How can I modify this code to be certain that each time I run r.bat, Excel will certainly run the next file or program on the loop only when the previous one has finished running in DOS?

Thanks to all the good people out there!!!!!!!

 

[TommyGun]

Use the ShellAndWait function posted on this thread.