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

mcs26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad KhanManager, ITCommented:
0
mcs26Author Commented:
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)
0
richardcardinCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

AbarajCommented:
Private Type STARTUPINFO
      cb As Long
      lpReserved As String
      lpDesktop As String
      lpTitle As String
      dwX As Long
      dwY As Long
      dwXSize As Long
      dwYSize As Long
      dwXCountChars As Long
      dwYCountChars As Long
      dwFillAttribute As Long
      dwFlags As Long
      wShowWindow As Integer
      cbReserved2 As Integer
      lpReserved2 As Long
      hStdInput As Long
      hStdOutput As Long
      hStdError As Long
   End Type

   Private Type PROCESS_INFORMATION
      hProcess As Long
      hThread As Long
      dwProcessID As Long
      dwThreadID As Long
   End Type

   Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
      hHandle As Long, ByVal dwMilliseconds As Long) As Long

   Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
      lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
      lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
      ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
      ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
      lpStartupInfo As STARTUPINFO, lpProcessInformation As _
      PROCESS_INFORMATION) As Long

   Private Declare Function CloseHandle Lib "kernel32" _
      (ByVal hObject As Long) As Long

   Private Declare Function GetExitCodeProcess Lib "kernel32" _
      (ByVal hProcess As Long, lpExitCode As Long) As Long

   Private Const NORMAL_PRIORITY_CLASS = &H20&
   Private Const INFINITE = -1&

   Public Function ExecCmd(cmdline$)
      Dim proc As PROCESS_INFORMATION
      Dim start As STARTUPINFO

      ' Initialize the STARTUPINFO structure:
      start.cb = Len(start)

      ' Start the shelled application:
      ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
         NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)

      ' Wait for the shelled application to finish:
         ret& = WaitForSingleObject(proc.hProcess, INFINITE)
         Call GetExitCodeProcess(proc.hProcess, ret&)
         Call CloseHandle(proc.hThread)
         Call CloseHandle(proc.hProcess)
         ExecCmd = ret&
   End Function

   Sub Form_Click()
      Dim retval As Long
      retval = ExecCmd("G:\WorkFolder\Mark\Test.bat")
      MsgBox "Process Finished, Exit Code " & retval
   End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcs26Author Commented:
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,
0
mcs26Author Commented:
Sorry should added for WScript to work is there a reference I need to add to my project?
0
Kamaraj SubramanianApplication Support AnalystCommented:
why dont you add some sleep time ?

0
mcs26Author Commented:
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
0
RobSampsonCommented:
Hi, this should work:
set wsh = createobject("WScript.Shell")
wsh.Run "c:\somebatchfile.bat", 0, 1

if you add a Reference to "Microsoft Scripting Runtime"

Rob.
0
mcs26Author Commented:
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
0
richardcardinCommented:
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.
0
RobSampsonCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.