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:[email protected]/" ^
    "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.

Martin Prikryl · Accepted Answer · 2020-10-16 13:48:47Z

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

edited Oct 16, 2020 at 13:48

answered Oct 16, 2020 at 6:42

Martin Prikryl

205k64 gold badges560 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Over a year ago

You're a genius, thank you, just needed a "then" after the If: If exitCode <> 0 Then

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related