Wait for shell command to complete [duplicate]

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?

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
– Magoo, Commented Apr 11, 2013 at 14:51
see stackoverflow.com/a/1439241/1176601 ( WaitForSingleObject ) — Aprillion
– Aprillion, Commented Apr 11, 2013 at 14:51

Community · Accepted Answer · 2017-05-23 12:10:34Z

100

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, 2017 at 12:10

CommunityBot

11 silver badge

answered Apr 11, 2013 at 14:52

Nate Hekman

6,6771 gold badge29 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

K_B Over a year ago

in .net, but not in VBA...

Nate Hekman Over a year ago

Ok, modified to use WScript.Shell.

user2946432 Over a year ago

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.

Qbik Over a year ago

doesn't work for me beacause of "Method 'Run' of object 'IWshShell3' failed" error number -2147024894

J.Goddard Over a year ago

It is possible it doesnt work for you because your path has spaces @Qbik @Santosh use wsh.Run Chr(34) & YourPathWithSpaces & Chr(34)

|

Anonymous · Accepted Answer · 2015-09-13 14:59:52Z

11

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.

answered Sep 13, 2015 at 14:59

Anonymous

1711 silver badge2 bronze badges

Comments

Asam · Accepted Answer · 2014-10-09 07:26:51Z

6

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

answered Oct 9, 2014 at 7:26

Asam

611 silver badge2 bronze badges

Comments

Unheilig · Accepted Answer · 2014-03-05 21:51:38Z

2

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)

edited Mar 5, 2014 at 21:51

Unheilig

16.3k193 gold badges71 silver badges101 bronze badges

answered Mar 5, 2014 at 21:27

geek0

211 bronze badge

1 Comment

Niall Over a year ago

This code doesn't work in VBA as there's no RunShell defined

dadj · Accepted Answer · 2014-07-18 12:20:22Z

2

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

answered Jul 18, 2014 at 12:20

dadj

211 bronze badge

Comments

Community · Accepted Answer · 2017-05-23 12:18:10Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Apr 11, 2013 at 14:52

K_B

3,6781 gold badge21 silver badges29 bronze badges

Comments

Borkman · Accepted Answer · 2014-05-08 16:05:34Z

-5

Dim wsh as new wshshell

chdir "Directory of Batch File"

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

Done Son

answered May 8, 2014 at 16:05

Borkman

1

1 Comment

Paul Lo Over a year ago

Don't understand your answer....

Collectives™ on Stack Overflow

Wait for shell command to complete [duplicate]

7 Answers 7

12 Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

12 Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

Linked

Related