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?

Answer 1

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)

Answer 2

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.