Wait for shell command to complete

Question

I'm running a simple shell command in Excel VBA that runs a batch file in a specified directory like below:

Dim strBatchName As String
strBatchName = "C:\folder\runbat.bat"
Shell strBatchName

Sometimes the batch file might take longer on some computer to run, and there are proceeding VBA code that is dependent on the batch file to finish running. I know you can set a wait timer like below:

Application.Wait Now + TimeSerial(0, 0, 5)

But that might not work on some computer that are too slow. Is there a way to systematically tell Excel to proceed with the rest of the VBA code until after the shell has finish running?

see http://stackoverflow.com/a/1439241/1176601 ( WaitForSingleObject ) — Aprillion, Apr 11 '13 at 14:51
Could you have the BATCH create a file when it's finished and VBA wait until that file's created? Or have batch delete a flag-file when it's finished and VBA waits until the flagfile is gone? — Magoo, Apr 11 '13 at 14:51

score 93 · Accepted Answer · edited May 23 '17 at 12:10

93

Use the WScript.Shell instead, because it has a waitOnReturn option:

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run "C:\folder\runbat.bat", windowStyle, waitOnReturn

(Idea copied from Wait for Shell to finish, then format cells - synchronously execute a command)

edited May 23 '17 at 12:10

Community

1
1

answered Apr 11 '13 at 14:52

Nate Hekman

6,507
27
30

2

in .net, but not in VBA... – K_B Apr 11 '13 at 14:55
1

Ok, modified to use WScript.Shell. – Nate Hekman Apr 11 '13 at 15:08
Since upgrading from 2010 to 2013 (and moving .xlam file to C:\Program Files\Microsoft Office 15\root\office15\Library\) I can no longer launch .exe files using this method. I'm assuming some soft of file permissions - but I've explicitly added myself as full control (I'm a member of Administrators too, which already seemed to have full rights) but I still get an error: The application was unable to start correctly (0xc000007b). Click OK to close the application. Any ideas/suggestions? – Terry Sep 27 '13 at 15:38
Actually have made a new question at http://stackoverflow.com/questions/19054973/excel-vba-no-longer-able-to-use-wscript-shell-run since it is slightly off topic. – Terry Sep 27 '13 at 15:49
7

This line in Nate's answer: wsh.Run("C:\folder\runbat.bat", windowStyle, waitOnReturn) gives a compilation error: "Expected: "=". When parentheses are removed, it works: wsh.Run "C:\folder\runbat.bat", windowStyle, waitOnReturn I am not sure if it pertains to the matter, but I am running Win8 and Excel 2013. – user2946432 Nov 01 '13 at 21:12
1

it needs to be `errorCode = wsh.Run("C:\folder\runbat.bat", windowStyle, waitOnReturn)` – John Janssen Jan 14 '15 at 13:16
Thanks @JohnJanssen. I think in SO world you're supposed to just feel free to edit the answer. I've now removed the errorCode variable to leave the answer focused on the problem. I've also removed the parentheses since a number of people have found them not to work. – Nate Hekman Jan 15 '15 at 16:06
True. Your edit does answer the main problem. The code that I added in the comment will return the exitCode from the program and will not fix the question. – John Janssen Jan 15 '15 at 16:59
*"Idea copied from Wait for Shell to finish, then format cells"* Attribution is good, but instead of copying an answer, the right thing to do is probably just to vote to close as duplicate in the first place. – Jean-François Corbett Sep 13 '15 at 15:06
2

doesn't work for me beacause of "Method 'Run' of object 'IWshShell3' failed" error number -2147024894 – Qbik Sep 26 '15 at 11:05
Doesn't works for me - VBA – Santosh May 17 '19 at 06:08
2

It is possible it doesnt work for you because your path has spaces @Qbik @Santosh use ```wsh.Run Chr(34) & YourPathWithSpaces & Chr(34)``` – J.Goddard Jul 23 '20 at 11:04

score 11 · Answer 2 · answered Sep 13 '15 at 14:59

Add the following Sub:

Sub SyncShell(ByVal Cmd As String, ByVal WindowStyle As VbAppWinStyle)
VBA.CreateObject("WScript.Shell").Run Cmd, WindowStyle, True
End Sub

If you add a reference to C:\Windows\system32\wshom.ocx you can also use:

Sub SyncShell(ByVal Cmd As String, ByVal WindowStyle As VbAppWinStyle)
Static wsh As New WshShell
wsh.Run Cmd, WindowStyle, True
End Sub

This version should be more efficient.

score 6 · Answer 3 · answered Oct 09 '14 at 07:26

Save the bat file on "C:\WINDOWS\system32" and use below code it is working

   Dim wsh As Object
   Set wsh = VBA.CreateObject("WScript.Shell")
   Dim waitOnReturn As Boolean: waitOnReturn = True
   Dim windowStyle As Integer: windowStyle = 1
   Dim errorCode As Integer

   errorCode = wsh.Run("runbat.bat", windowStyle, waitOnReturn)

If errorCode = 0 Then
    'Insert your code here
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If

score 2 · Answer 4 · edited Mar 05 '14 at 21:51

This is what I use to have VB wait for process to complete before continuing. I did not write this and do not take credit.

It was offered in some other open forum and works very well for me:

The following declarations are needed for the RunShell subroutine:

Option Explicit

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

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

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

Private Const PROCESS_QUERY_INFORMATION = &H400

Private Const STATUS_PENDING = &H103&

'then in your subroutine where you need to shell:

RunShell (path and filename or command, as quoted text)

This code doesn't work in VBA as there's no RunShell defined — Niall, May 08 '14 at 16:07

score 2 · Answer 5 · answered Jul 18 '14 at 12:20

what you proposed with a change at the parenthesis at the Run command worked fine with VBA for me

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Integer
wsh.Run "C:\folder\runbat.bat", windowStyle, waitOnReturn

score 0 · Answer 6 · edited May 23 '17 at 12:18

0

Either link the shell to an object, have the batch job terminate the shell object (exit) and have the VBA code continue once the shell object = Nothing?

Or have a look at this: Capture output value from a shell command in VBA?

edited May 23 '17 at 12:18

Community

1
1

answered Apr 11 '13 at 14:52

K_B

3,668
1
19
29

score -4 · Answer 7 · answered May 08 '14 at 16:05

-4

Dim wsh as new wshshell

chdir "Directory of Batch File"

wsh.run "Full Path of Batch File",vbnormalfocus, true

Done Son

answered May 08 '14 at 16:05

Borkman

1

Don't understand your answer.... – Paul Lo May 08 '14 at 16:23

Wait for shell command to complete

7 Answers7

Linked