Wait for shell command to complete [duplicate]

Question

This question already has an answer here:

• Wait for Shell to finish, then format cells - synchronously execute a command 5 answers

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

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?

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.