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

Question

I have an executable that I call using the shell command: ``` Shell (ThisWorkbook.Path & "\ProcessData.exe") ``` The executable does some computations, then exports results back to Excel. I want to be able to change the format of the results AFTER they are exported. In other words, i need the Shell command first to WAIT until the executable finishes its task, exports the data, and THEN do the next commands to format. I tried the `Shellandwait()`, but without much luck. I had: ``` Sub Test() ShellandWait (ThisWorkbook.Path & "\ProcessData.exe") 'Additional lines to format cells as needed End Sub ``` Unfortunately, still, formatting takes place first before the executable finishes. Just for reference, here was my full code using ShellandWait ``` ' Start the indicated program and wait for it ' to finish, hiding while we wait. Private Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long Private Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal hHand…

Accepted Answer

Try the WshShell object instead of the native `Shell` function.

```
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 Long

errorCode = 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 String
pth = """" & ThisWorkbook.Path & "\ProcessData.exe" & """"
errorCode = wsh.Run(pth , windowStyle, waitOnReturn)
```

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

Solution

Problem