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

Try the WshShell object instead of the native Shell function.

Dim wsh As ObjectSet wsh = VBA.CreateObject("WScript.Shell")Dim waitOnReturn As Boolean: waitOnReturn = TrueDim windowStyle As Integer: windowStyle = 1Dim errorCode As LongerrorCode = wsh.Run("notepad.exe", windowStyle, waitOnReturn)If errorCode = 0 Then    MsgBox "Done! No error to report."Else    MsgBox "Program exited with error code " & errorCode & "."End If    

Though note that:

If bWaitOnReturn is set to false (the default), the Run method returns immediately after starting the program, automatically returning 0 (not to be interpreted as an error code).

So to detect whether the program executed successfully, you need waitOnReturn to be set to True as in my example above. Otherwise it will just return zero no matter what.

For early binding (gives access to Autocompletion), set a reference to "Windows Script Host Object Model" (Tools > Reference > set checkmark) and declare like this:

Dim wsh As WshShell Set wsh = New WshShell

Now to run your process instead of Notepad... I expect your system will balk at paths containing space characters (...\My Documents\..., ...\Program Files\..., etc.), so you should enclose the path in "quotes":

Dim pth as Stringpth = """" & ThisWorkbook.Path & "\ProcessData.exe" & """"errorCode = wsh.Run(pth , windowStyle, waitOnReturn)

What you have will work once you add

Private Const SYNCHRONIZE = &H100000

which your missing. (Meaning 0 is being passed as the access right to OpenProcess which is not valid)

Making Option Explicit the top line of all your modules would have raised an error in this case

The WScript.Shell object's .Run() method as demonstrated in Jean-François Corbett's helpful answer is the right choice if you know that the command you invoke will finish in the expected time frame.

Below is SyncShell(), an alternative that allows you to specify a timeout, inspired by the great ShellAndWait() implementation. (The latter is a bit heavy-handed and sometimes a leaner alternative is preferable.)

' Windows API function declarations.Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As LongPrivate Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As LongPrivate Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As LongPrivate Declare Function GetExitCodeProcess Lib "kernel32.dll" (ByVal hProcess As Long, ByRef lpExitCodeOut As Long) As Integer' Synchronously executes the specified command and returns its exit code.' Waits indefinitely for the command to finish, unless you pass a ' timeout value in seconds for `timeoutInSecs`.Private Function SyncShell(ByVal cmd As String, _                           Optional ByVal windowStyle As VbAppWinStyle = vbMinimizedFocus, _                           Optional ByVal timeoutInSecs As Double = -1) As Long    Dim pid As Long ' PID (process ID) as returned by Shell().    Dim h As Long ' Process handle    Dim sts As Long ' WinAPI return value    Dim timeoutMs As Long ' WINAPI timeout value    Dim exitCode As Long    ' Invoke the command (invariably asynchronously) and store the PID returned.    ' Note that this invocation may raise an error.    pid = Shell(cmd, windowStyle)    ' Translate the PIP into a process *handle* with the    ' SYNCHRONIZE and PROCESS_QUERY_LIMITED_INFORMATION access rights,    ' so we can wait for the process to terminate and query its exit code.    ' &H100000 == SYNCHRONIZE, &H1000 == PROCESS_QUERY_LIMITED_INFORMATION    h = OpenProcess(&H100000 Or &H1000, 0, pid)    If h = 0 Then        Err.Raise vbObjectError + 1024, , _          "Failed to obtain process handle for process with ID " & pid & "."    End If    ' Now wait for the process to terminate.    If timeoutInSecs = -1 Then        timeoutMs = &HFFFF ' INFINITE    Else        timeoutMs = timeoutInSecs * 1000    End If    sts = WaitForSingleObject(h, timeoutMs)    If sts <> 0 Then        Err.Raise vbObjectError + 1025, , _         "Waiting for process with ID " & pid & _         " to terminate timed out, or an unexpected error occurred."    End If    ' Obtain the process's exit code.    sts = GetExitCodeProcess(h, exitCode) ' Return value is a BOOL: 1 for true, 0 for false    If sts <> 1 Then        Err.Raise vbObjectError + 1026, , _          "Failed to obtain exit code for process ID " & pid & "."    End If    CloseHandle h    ' Return the exit code.    SyncShell = exitCodeEnd Function' ExampleSub Main()    Dim cmd As String    Dim exitCode As Long    cmd = "Notepad"    ' Synchronously invoke the command and wait    ' at most 5 seconds for it to terminate.    exitCode = SyncShell(cmd, vbNormalFocus, 5)    MsgBox "'" & cmd & "' finished with exit code " & exitCode & ".", vbInformationEnd Sub