Wait for shell command to complete [duplicate]
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)
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.
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