Best option for simple FTP/SFTP operations via VBA ?

[Isaac]

Does anyone have a recommendation for a simple, VBA- accessible solution to get and put files via sftp or FTP?

I'm looking for something that has MINIMAL conflicts with firewall, etc., (so I guess passive ftp? which built in windows no longer allows).

I tried psftp.exe, which I'd used a few years ago, but even their page mentions OS that support it, and windows 7/windows 10 not even included. I tried just opening psftp.exe and manually doing "open host.host.com", and the screen immediately disappeared.

I'm OK installing a 3rd party tool - but somethign you know for sure works w/any sample scripts would be appreciated. Very simple put and get, don't even need to change a directory

 

[pbaldy]

It's been a while, but I've used this successfully.

Modules: Internet Data Transfer Library

 

[Pat Hartman]

I had an app that ran a script to transfer data. This was the Access code. I'm pretty sure the script would be dependent on whatever FTP tool you were using. It was a long time ago. The details are fuzzy. I think we were using FileZilla.

    ' Run WinSCP to upload the files to the DSS server.
    strQuote = Chr(34)
    strSFTPDir = "c:\Program Files\WinSCP\"
    strCommand = "/script=" & CurrentProject.Path & "\WinSCPUpload.txt"
    Call Shell(strSFTPDir & "winscp.com " & strQuote & strCommand & strQuote, vbNormalNoFocus)
    DoEvents

Here's the contents of WinSCPUpload.txt:

option batch abort
option confirm off
open Prod
cd /input
lcd S:\Pat\SendFilesAccess
put S:\Pat\SendFilesAccess\cpscent.20151214.dat2.zip
exit

 

[Isaac]

Update - I did end up using WinSCP.

I was kind of expecting the 'worst', I figured I'd be able to get some sample scripts from their documentation, but then end up in some pickle that I couldn't solve and have to wade through stack overflow posts until the cows came home, like I used to have to do with psftp.exe

Very happily, I discovered that WinSCP has a very cool tool. First, it comes with a full-fledged GUI (kind of like Filezilla only you can automate it so it's worth something!). While working in this full-fledged GUI, you can first connect to a site, and then begin the Transfer process for a file. Just before you click the final "OK", you click on a button "Generate Code". Once clicked, it literally generates two sets of code:

1) the code for the Script file
2) suggested code for the bat file, or at the command prompt

It includes the login, any directory changes already indicated in your current GUI status, and also includes any protocol related codes the GUI has already figured out for you by you logging in.

Of course then I had to write the VBA code to create the script file, the bat file, execute it, test for successful download etc. etc., but that was a snap once I got the joy of it working.

Thank You WinSCP !!

Kinda miss the old days when the Windows FTP client was useful, but oh well. Change comes. As long as Windows stops trying to become like Apple where we can't do anything with our damn computers, I guess I'll be OK.

 

[shadow9449]

Sounds like you're happy with your solution. Just to add to the thread: I use PuTTY for sftp. It's a pretty simple shell command and didn't take long for me to figure out.

 

[Isaac]

Sounds like you're happy with your solution. Just to add to the thread: I use PuTTY for sftp. It's a pretty simple shell command and didn't take long for me to figure out.

That's the same as psftp.exe, right?

 

[shadow9449]

That's the same as psftp.exe, right?

Yes, in the sense that the shell command calls psftp.exe.

 

[Isaac]

I've used psftp.exe before with command line programming, but this time it would disappear with no warning on Windows 10. I know there may be a solution to it, but their documentation mentioned that if anything went wrong, "Caution: If a user mistypes any information, then there are not able to reissue it as the PSFTP will terminate it immediately."

I guess WinSCP appealed to me b/c of the GUI + generate code....now i'm hooked.
But I'll keep putty in my back pocket in case it still comes in handy some day!