How to catch Windows shell script errors launched from VBA code?

Question

I'm launching a batch script from VBA with the Shell function:

myRes = Shell("myScript.cmd")

Is there a way to know if it runs successfully or if there were execution errors?

Answer 1

I suggest you 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 'or whatever suits you best
Dim errorCode As Integer

errorCode = wsh.Run("myScript.cmd", windowStyle, waitOnReturn)

If errorCode = 0 Then
    MsgBox "Execution successful. 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.

This earlier answer of mine may be helpful as well.

Answer 2

You can catch the error level if you are that the command will return 0 on success: http://www.devx.com/vb2themax/Tip/18663

Get the exit code of a process

In a few cases, in particular when running MsDos batch files from within a VB application, you may want to determine the ERRORLEVEL set by an external application. You can't do it with a plain Shell statement, but the job becomes easy with the support of the GetProcessExitCode API function:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
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
Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400


Private Sub cmdRunNotepad_Click()
    Dim hTask As Long
    Dim hProcess As Long
    Dim exitCode As Long

    hTask = Shell("Notepad", vbNormalFocus)
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hTask)

    ' loop until the process returns a valid exit code
    Do
        ' relinquish this CPU time slice
        Sleep 100
        DoEvents
        ' query for exit code
        GetExitCodeProcess hProcess, exitCode
    Loop While exitCode = STILL_ACTIVE

    MsgBox "Exit code = " & exitCode, vbInformation


End Sub

Francesco Balena

or you can try something like this:

myRes = Shell("cmd /c myScript.cmd&&echo success")

here's more info about conditional execution: http://www.robvanderwoude.com/condexec.php

But in both cases you rely on exit codes.