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:[email protected]/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?

Martin Prikryl · Accepted Answer · 2018-05-30 06:26:32Z

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

edited May 30, 2018 at 6:26

answered May 29, 2018 at 14:24

Martin Prikryl

205k64 gold badges560 silver badges1.1k bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related