Download a file from SFTP site using VBA RRS feed

  • Question

  • 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? 



    Thursday, May 17, 2012 6:58 AM


  • 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.


    • Marked as answer by Kenrav Thursday, May 17, 2012 2:00 PM
    Thursday, May 17, 2012 1:59 PM
  • 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 & " " & strQuote & strCommand & strQuote, vbNormalNoFocus)

    As you can see, it simply shells out to 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 s
    # 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

    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.


    Saturday, May 19, 2012 3:08 AM

All replies