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
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
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.b at"
Retval = Shell(ScriptPath, vbNormalFocus)
Dim ScriptPath As String
Dim Retval
ScriptPath = "G:\WorkFolder\Mark\Test.b
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Shel l")
wsh.Run "c:\somebatchfile.bat", 0, 1
Thanks again,
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.Shel
wsh.Run "c:\somebatchfile.bat", 0, 1
Thanks again,
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 ?
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
Sub Pause(DelayInSeconds As Double)
Dim StartTime As Double
StartTime = Timer
Do While Timer < StartTime + DelayInSeconds
DoEvents
Loop
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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.
Anyway, also try this:
wsh.Run "cmd /c c:\somebatchfile.bat", 0, 1
Regards,
Rob.
detail can be seen here
http://support.microsoft.com/default.aspx?scid=http://support.microsoft..com:80/support/kb/articles/Q129/7/96.asp&NoWebContent=1