Run PowerShell FTP upload script from VBA and issue message box with the result

Question

I have a basic FTP upload PowerShell code that I run (hidden) via VBA in Excel:

VBA:

Call Shell("powershell -executionpolicy bypass & """ & ThisWorkbook.Path & "\FTP\FTPUpload.ps1""", vbHide)

FTPUpload.ps1:

$File = "H:\Workbook\file.txt"
$ftp ="ftp://user:pass@ftp.site.org/incoming/file.txt"

"ftp url: $ftp"

$webclient = New-Object System.Net.WebClient
$uri = New-Object System.Uri($ftp)

"Uploading $File..."

$webclient.UploadFile($uri, $File)

I want to be able to display a pass/fail message to the user. What is the best way to do this?

Answer 1

Make the PowerShell script signal its results by an exit code:

try
{
    $webclient = New-Object System.Net.WebClient
    $uri = New-Object System.Uri($ftp)

    "Uploading $File..."

    $webclient.UploadFile($uri, $File)

    exit 0
}
catch
{
    exit 1
}

---

And modify your VBA code to wait for the exit code and act accordingly.
See VBA Shell and Wait with Exit Code