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

User · Answer

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    amp H400  Private Const STATUS PENDING    amp H103 amp    then in your subroutine where you need to shell   RunShell  path and filename or command  as quoted text

User · Answer

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

User · Answer

Dim wsh as new wshshell  chdir  Directory of Batch File   wsh run  Full Path of Batch File  vbnormalfocus  true  Done Son

User · Answer

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

User · Answer

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    amp  errorCode  amp      End If

User · Answer

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

User · Answer

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

[excel] Wait for shell command to complete

Examples related to excel

Examples related to shell

Examples related to vba

Examples related to batch-file