Return the status of an FTP upload using WinSCP batch scripting executed from VBA?

Question

I am sharing an Excel workbook with multiple users who are executing a macro that executes the following WinSCP batch script:

"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
  /command ^
    "open ftp://user:pass@ftp.website.org/" ^
    "cd /incoming/data" ^
    "put ""%~dp0file.txt""" ^
    "exit"

set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
  echo Success
) else (
  echo Error
)

exit /b %WINSCP_RESULT%

The script is executed from VBA as follows:

Call Shell("C:\Users\" & Environ("username") & "\Sharepoint - Library Folder\FTP\ftpupload.bat")

When executed, the command window appears for 1-2 seconds and goes away. Is there a way to leave it up with the Success/Error result or even better would be to pass it back to VBA so I can display the result in an Ok-Window?

Note: I'd like to avoid having to register the WinSCP COM in VBA as this workbook is being used by multiple people and I need to keep it simple with as little prerequisites as possible.

Answer 1

Your batch file already returns exit code indicating an error/success.

So all you need is to capture the code and act accordingly.
For that, see Is it possible to return error code to VBA from batch file?

Set oSHELL = VBA.CreateObject("WScript.Shell")
Dim exitCode As Integer
exitCode = oSHELL.Run("""C:\Users\" & Environ("username") & "\Sharepoint - Library Folder\FTP\ftpupload.bat""", 0, True)
If exitCode <> 0 Then
    MsgBox "Failed", vbOKOnly, "Failure"
Else
    MsgBox "Succeeded", vbOKOnly, "Success"
End If