Error check doesn't work for batch run in VBA Excel

[cercig]

I have a VBA-code which runs a batch file (bat file). The code runs without error when I run the batch file directly, and I get the correct result:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub RunSASCodeViaBatFile()
Dim cmdString As String: cmdString = batPath & batFile & " " & SASFilePath & " " & SASFile & " " & SASOutputPath & " " & YearMonth
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 2

wsh.Run cmdString, windowStyle, waitOnReturn
End Sub</code>However, when I try to run the script with an error check method like below, then the message box gives a message like:

Program exited with error code 1001
​

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Public Sub RunSASCodeViaBatFile()
Dim cmdString As String: cmdString = batPath & batFile & " " & SASFilePath & " " & SASFile & " " & SASOutputPath & " " & YearMonth
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 2
Dim errorCode As Integer
errorCode = wsh.Run(cmdString, windowStyle, waitOnReturn)

If errorCode <> 0 Then
MsgBox "Program exited with error code " & errorCode & "."
End If

End Sub</code>I get that message, but I also get "the correct result" as I want in anyway. So it seems like that if-loop gives that message in anyway. It is a bit difficult to understand, because I have seen many other other people who used the same method.

 

[Steve_R]

Try this method:

Public Sub RunSASCodeViaBatFile()

    Dim cmdString As String
    Dim wsh As Object
    Dim waitOnReturn As Boolean 
    Dim windowStyle As Integer 
    Dim errorCode As Integer

    On Error GoTo RunSASCodeViaBatFile_Error

    waitOnReturn = True
    windowStyle = 2
    cmdString = batPath & batFile & " " & SASFilePath & " " & SASFile & " " & SASOutputPath & " " & YearMonth
    Set wsh = VBA.CreateObject("WScript.Shell")
    
'The following line will raise an error. If you wish to test, uncomment the line
  'Err.Raise (1001)
'Test a different error number to see the difference
  

RunSASCodeViaBatFile_Exit: 'Normal exit point if no error
' Put code here that tidies up.
' For example if you turn off screen updating or events, change calculation etc

    Exit Sub

'*******************************************
' Code below never runs unless there is an error
RunSASCodeViaBatFile_Error:
    Select Case Err

    Case Is = 1001

        MsgBox "This is what happens when you get error 1001"

    Case Else

        MsgBox "Error " & Err & " - " & Err.Description & vbCrLf & "Add this error number to your error trap if you need to change what the error trap does for this error"

    End Select

    GoTo RunSASCodeViaBatFile_Exit 'go back to normal exit point
    
    End Sub

As you get other errors and if they need to be handled differently, add the error number to the trap.

For example, if you wish the sub just to exit with no message for error 1004

   Select Case Err

    Case Is = 1001

        MsgBox "This is what happens when you get error 1001"
    Case Is = 1004
 
' do nothing
    Case Else

        MsgBox "Error " & Err & " - " & Err.Description & vbCrLf & "Add this error number to your error trap if you need to change what the error trap does for this error"

    End Select

Case is = {another specific error number}
Resume next

Will continue code on the next line after the error but I can't imagine it being useful for what you are doing.

 

[cercig]

Try this method:

Public Sub RunSASCodeViaBatFile()

    Dim cmdString As String
    Dim wsh As Object
    Dim waitOnReturn As Boolean 
    Dim windowStyle As Integer 
    Dim errorCode As Integer

    On Error GoTo RunSASCodeViaBatFile_Error

    waitOnReturn = True
    windowStyle = 2
    cmdString = batPath & batFile & " " & SASFilePath & " " & SASFile & " " & SASOutputPath & " " & YearMonth
    Set wsh = VBA.CreateObject("WScript.Shell")
    
'The following line will raise an error. If you wish to test, uncomment the line
  'Err.Raise (1001)
'Test a different error number to see the difference
  

RunSASCodeViaBatFile_Exit: 'Normal exit point if no error
' Put code here that tidies up.
' For example if you turn off screen updating or events, change calculation etc

    Exit Sub

'*******************************************
' Code below never runs unless there is an error
RunSASCodeViaBatFile_Error:
    Select Case Err

    Case Is = 1001

        MsgBox "This is what happens when you get error 1001"

    Case Else

        MsgBox "Error " & Err & " - " & Err.Description & vbCrLf & "Add this error number to your error trap if you need to change what the error trap does for this error"

    End Select

    GoTo RunSASCodeViaBatFile_Exit 'go back to normal exit point
    
    End Sub

As you get other errors and if they need to be handled differently, add the error number to the trap.

For example, if you wish the sub just to exit with no message for error 1004

   Select Case Err

    Case Is = 1001

        MsgBox "This is what happens when you get error 1001"
    Case Is = 1004
 
' do nothing
    Case Else

        MsgBox "Error " & Err & " - " & Err.Description & vbCrLf & "Add this error number to your error trap if you need to change what the error trap does for this error"

    End Select

Case is = {another specific error number}
Resume next

Will continue code on the next line after the error but I can't imagine it being useful for what you are doing.

Thanks a lot for the help. Now I changed the error-handling method from mine to yours, however I get the same error 1001 with your method also. As I wrote before, I don't get any error and I get my expected result without error-handling. When I apply error-handling, I still get my expected result, but I get this error number 1001 for some reason. A bit difficult to understand why.

 

[cercig]

Sorry, I wrote wrong in the previous reply. Now it works without error with your error-handling. I just uncommented that Err.Raise (1001) before, that's why I got the same error
Thanks a lot again...