Download a file from SFTP site using VBA

  • Article

Question

Thursday, May 17, 2012 6:58 AM

I need to:

1. Connect to a SFTP site (using username and password.)

2. Look in a remote directory to see if there are any files.

3. Download any files to my local drive and killing them after the download.  Unfortunately, I won't know their names ahead of time (maybe I could do a wildcard?) 

Any suggestions? 

Thanks.

Ken

All replies (8)

Thursday, May 17, 2012 1:59 PM ✅Answered

After I responded to jdweng's reply, I found great documentation for the WinSCP client and have been able to accomplish my task.  My thanks to jdweng and Daniel for your replies.

Ken

Saturday, May 19, 2012 3:08 AM ✅Answered

This actually turned out to be quite simple. 

First, I downloaded and installed a royalty-free SFTP client called WinSCP.  Fortunately, it has console and scripting functionalty along with great documentation.

Second, in my app, I created a function that has the following code:

Dim strQuote As String
strQuote = Chr(34)   
Dim strSFTPDir As String
strSFTPDir = "c:\program files\winscp\   
Dim strCommand As String
strCommand = "/script=c:\test\abc\WinSCPGet.txt"   
Call Shell(strSFTPDir & "winscp.com " & strQuote & strCommand & strQuote, vbNormalNoFocus)

As you can see, it simply shells out to winscp.com and executes a script.

Finally, I wrote a script ("WinSCPGet.txt") using the commands and examples I found in the documemtation:

# Connect to the host and login using password
open sftp://user:password.host.com
# Change the remote directory
cd /home/from/
# get all the files in the remote directory and download them to a specific local directory
get *.* c:\test\from\
# remove the files from the remote direcory
rm *.*
# Close and terminate the session
exit

Works great. Of course, you can do a whole lot more with the commands WinSCP offers including the use of private and public keys, transferring remote files to other remote directories, etc.

I hope this is useful.

Ken

Thursday, May 17, 2012 12:42 PM

Do you  mean SFTP or FTP.  FTP has a mget which will get multiple files in a folder.

jdweng

Thursday, May 17, 2012 12:59 PM | 1 vote

If you google the subject you will find a ton of examples to learn from, such as:

http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm
http://www.vbusers.com/downloads/download.asp#item16
http://access.mvps.org/access/modules/mdl0037.htm
http://www.access-programmers.co.uk/forums/showthread.php?t=178371

Daniel Pineault, 2010 Microsoft MVP
http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Thursday, May 17, 2012 1:03 PM

SFTP (SSH File Transfer Protocol).  I've done FTP in the past.  I've even uploaded files to an SFTP site.  However, downloading has been problematic.

Ken

Thursday, May 17, 2012 2:01 PM

If you can, please post your solution so it can benefit anyone else trying to do this.

Daniel Pineault, 2010 Microsoft MVP
http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Thursday, June 5, 2014 3:30 PM

open sftp://user:password.host.com

It should be "sftp://user:password@host.com". Note the "at" sign" instead of "dot".

Monday, September 12, 2016 10:25 AM

Hi, it would be great if you can provide the full code. i am trying to download file from sftp3 through winscp using vba.

thanks in advnace!