[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Run MS DOS BAT file from a VBA script

Posted on 2010-03-25
12
Medium Priority
?
898 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:mcs26
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 17

Expert Comment

by:Muhammad Khan
ID: 28543986
0
 

Author Comment

by:mcs26
ID: 28548905
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
 
LVL 5

Expert Comment

by:richardcardin
ID: 28550301
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 3

Accepted Solution

by:
Abaraj earned 1000 total points
ID: 28550335
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
 

Author Comment

by:mcs26
ID: 28551417
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
 

Author Comment

by:mcs26
ID: 28551881
Sorry should added for WScript to work is there a reference I need to add to my project?
0
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 28554997
why dont you add some sleep time ?

0
 

Author Comment

by:mcs26
ID: 28556701
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
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 1000 total points
ID: 28601313
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
 

Author Comment

by:mcs26
ID: 28663085
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
 
LVL 5

Expert Comment

by:richardcardin
ID: 28676219
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 28756767
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question