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

by

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)

More Related Contents:

  1. Capture output value from a shell command in VBA?
  2. Execute a command in command prompt using excel VBA
  3. VBA Shell and Wait with Exit Code
  4. Open folder in new instance
  5. Copy data from cell in selected row
  6. Why does Range work, but not Cells?
  7. Assign event handlers to controls on user form created dynamically in VBA
  8. Programmatic Access To Visual Basic Project Is Not Trusted
  9. Can I make this macro more efficient or faster?
  10. Conditional Formatting using Excel VBA code
  11. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  12. How to get the background color from a Conditional Formatting in Excel using VBA
  13. VBA Excel Populate ListBox with multiple columns
  14. VBA Integer Overflow at 70,000
  15. How to count up text of a different font colour in excel
  16. VBA Macro to compare all cells of two Excel files
  17. Automate saveas dialogue for IE9 (vba)
  18. Sending HTTP requests with VBA from Word
  19. What does ReDim Preserve do?
  20. Excel (Office 365) restarts automatically while executing UDF
  21. Running VBA from a HYPERLINK()
  22. Switching the FROM Inbox
  23. Xmlhttp request is raising an Access Denied error
  24. How can I execute a shell command using VBA?
  25. Remove Duplicates from range of cells in excel vba
  26. Pass array to ParamArray
  27. Iterate all email items in a specific Outlook folder
  28. Fastest way to check if two ranges are equal in excel vba [duplicate]
  29. Hiding active workbook programmatically in Excel
  30. Excel VBA Project has generated multiple Workbook objects

Leave a Comment