Is it possible to return error code to VBA from batch file?

Question

My batch file:

SET username=%1
SET password=%2

net use "\\gessel\toys\name" %password% /user:shops\%username%
ECHO.%ERRORLEVEL%
:copy
Xcopy "\\gessel\toys\name\babytoys" "%appdata%\shops" /S /E
ECHO.%ERRORLEVEL%

IF ERRORLEVEL 0 goto disconnect
goto end
:disconnect 
net use "\\gessel\toys\name\babytoys" /delete
goto end
:end
EXIT /B %ERRORLEVEL%

I have called above batch file from VBA and the code as follows:

call Shell(Environ$("COMSPEC") & " /c " & path & username & password, vbHide)

The above code is works fine. But I need to validate whether the files are copied or not in the VBA. Suppose the customer entered his username and password wrongly then he won't get the toys info. Then I have to display a message box that display message as "entered information wrong". So for that I have tried the code like this:

sub submit_click

    Dim as error as integer
    error = Shell(Environ$("COMSPEC") & " /c " & path & username & password, vbHide)
    if error <> 0
        MsgBox "Provided info wrong", vbOKOnly, "Failure"
    end if
end sub

But the above code does not work. It always returns the value even the username and password is correct. But if I run the batch file it correctly returns value such as for correct details 0 and for wrong data is 2 or 4. Please anyone help me to capture error code from batch file and to pass it into VBA.

Answer 1

The value of ERRORLEVEL variable changes with each command execution (more or less). So as the code in your batch file is executing, each command generates a change. You need to store the value for later processing or, in your case, exit with the adecuated value in each of the steps:

SET "username=%~1"
SET "password=%~2"

rem This will be tested for a errorlevel value of 1 or greater. In this case, 
rem no further processing will be done if errors found, so we return our own 
rem value to the calling process

net use "\\gessel\toys\name" %password% /user:shops\%username% 
if errorlevel 1 exit /b 100

rem We are connected, and will disconnect no matter if xcopy worked or failed
rem So, the result of the command will be stored to later return the adecuate value

Xcopy "\\gessel\toys\name\babytoys" "%appdata%\shops" /S /E
set "exitCode=%errorlevel%"

net use "\\gessel\toys\name\babytoys" /delete

EXIT /B %exitCode%

Now, in the vba code the error variable can be tested for value 100 (what we returned from errors in net use) or for any of the values returned from xcopy.

Now that we have a working batch file, let's to to Excel.

NO, Shell function in VBA can not do what you are asking. The return value of Shell is the id of the running process. Shell do not wait for the process to end, so, it can not return its exit code.

BUT, the WshShell object can do what you need.

Dim oSHELL, batchFile, user, password, exitCode
    Set oSHELL = VBA.CreateObject("WScript.Shell")
    batchFile="myBatchFile.cmd"
    user="myUserName"
    password="this is my password"

    ' Quote strings to avoid problems with spaces
    ' The arguments are WhatToRun, WindowStyle (0=hide), WaitForProcessToEnd
    exitCode = oSHELL.Run(""""+batchFile+""" """+user+""" """+password+"""", 0, True)