VBA Macro to execute bat file

[nzepeda]

I have written a bat file which works fine when I manually execute it or when I setup a schedule to run it. I was able to write some code in VBA that will execute the file.

The issue I am having is that for some reason when it executes, the bat file runs through each line super fast. Normally the bat file is supposed to open up a cmd prompt saying "Please wait for a little bit" while on the background another program is running grabbing a file off an ftp. This file is then transfered to another folder and another program runs grabbing certain info from it and coping that to a txt file. Once this is done the cmd prompt goes away and another program executes displaying info from the txt.

What I receive right now with the VBA code is a cmd prompt flashes for a second, program runs accessing the ftp, while this is running it tries coping over the file that has yet to be downloaded from the ftp. The other program that is supposed to get files from this file hangs up since it doesn't see the file. The first program that ran accessing the ftp finally stops, and the last program thats supposed to display runs.

Here is the code I have for VBA to run the bat file.

Sub Test()
Dim argh As Double
argh = Shell("c:\folder\test.bat", vbNormalFocus)
End Sub

This runs when certain text is received from the user. Once I get this running properly, it will run when certain text is being displayed on the screen.

 

[Glory]

Try messing around with DoEvents and While wEnd. Let me see if I can find an example I saw on here once.

 

[John_w]

Changing the Shell command to:

argh = Shell("cmd /k c:\folder\test.bat", vbNormalFocus)

will keep the command window open so that you can see the executed commands and any errors.

 

[Glory]

This is the post I was thinking about.

With the focus on this line:

While ieApp.Busy And ieApp.ReadyState <> 4: DoEvents: Wend

 

[Ruddles]

Can you not rewrite the batch file in VBA?

 

[nzepeda]

Thanks for your help, it turns out it wasn't an issue with the VBA code, I had changed something in my bat file when I first received an error in VBA and that ended up changing how my bat file ran. Once I corrected that the code I had written in VBA worked great.

 

[Ruddles]

I would still favour a solution which didn't shell out to a batch file.