Link to home
Start Free TrialLog in
Avatar of mcs26
mcs26

asked on

Run MS DOS BAT file from a VBA script

Hi,

Basically im trying to run a batch file from Access VBA. The batch file work fine if I double click it to run. The problem is the Shell calls the batch file but my VBA program doesn't wait until the batch program has finished running. consequently nothing happens other than the flash of the dos screen.

Any help would be great,

Mark

Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image

Avatar of mcs26
mcs26

ASKER

Sorry I do not quite know to change that code sample to get what I need.

Dim ScriptPath As String
Dim Retval

ScriptPath = "G:\WorkFolder\Mark\Test.bat"

Retval = Shell(ScriptPath, vbNormalFocus)
Try putting a pause at the end of the Test.bat file, so when it is executed, you will have a chance to see the code running and if there is an error in it.
ASKER CERTIFIED SOLUTION
Avatar of Abaraj
Abaraj

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mcs26

ASKER

The code above executes but nothing happens still. The batch file is supposed to copy a file from an FTP folder to a local file. It works fine if I double click the batch file so there is no error there. All I can find on the web is that VBA needs to wait for the Shell to finish running.

I have another solution that some seemed to work for someone (see below). However I get a runtime error of Method 'Run' of object IWshShell3 failed.

dim wsh
set wsh = createobject("WScript.Shell")
wsh.Run "c:\somebatchfile.bat", 0, 1

Thanks again,
Avatar of mcs26

ASKER

Sorry should added for WScript to work is there a reference I need to add to my project?
why dont you add some sleep time ?

Avatar of mcs26

ASKER

I tried the code below but made no difference.

Sub Pause(DelayInSeconds As Double)
   
    Dim StartTime As Double
    StartTime = Timer
    Do While Timer < StartTime + DelayInSeconds
          DoEvents
    Loop

End Sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mcs26

ASKER

Hi Rob,

I added the reference and ran the code but still appears VBA is forcing the batch file to exit before it is has finished. Cannot believe it should be this difficult, running of out ideas!

Thanks
In the bat file, can you add a few of these lines in?
ping localhost

Each you add should give you script 4 seconds. Just insert them after the process that needs more time.
You still see a DOS screen flash up?  Is it possible that your batch file is calling other programs, and it's the batch file that's not waiting for those to finish?

Anyway, also try this:

wsh.Run "cmd /c c:\somebatchfile.bat", 0, 1

Regards,

Rob.