downloading file using SFTP with VBA

Question

My objective is to download, not upload a file from an SFTP server, and I am trying to adapt the code from another question on this site to do so (I pasted the code below for your convenience).

I downloaded PSFTP from Putty. PSFTP closes when I try to connect using the following command line:

open username:[email protected]:1111 

I have three questions:

1. Is something wrong with my command line? If not then what could be the problem?

2. As far as I know SFTP would normally utilize get/put commands, but i don't see a put command in the code below, so I don't understand where I should enter the get command to download the file instead of uploading it (which is what the code below is supposed to be doing).

3. Is it correct that pRemotePath is the location of the file on the SFTP server, and pFile is the location I want the file downloaded to?

A simple explanation would be very much appreciated.

Public Sub SftpGet()

    Const cstrSftp As String = """C:\Users\Ron\UtilityTools\psftp.exe"""
    Dim strCommand As String
    Dim pUser As String
    Dim pPass As String
    Dim pHost As String
    Dim pFile As String
    Dim pRemotePath As String

    pUser = "uid"
    pPass = "PW"
    pHost = "dns"
    pFile = "C:\Users\Ron\activity.txt" 
    pRemotePath = "Z:/activity.log"

    strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
        " " & pFile & " " & pHost & ":" & pRemotePath
    Debug.Print strCommand
    Shell strCommand, 1 ' vbNormalFocus '
End Sub

Answer 1

I think you should start with a Windows command prompt session. Work out the details of your command line there, as I suggested in an answer to a similar question: SFTP upload with VBA. Once you have a command line which works there, it will be very easy to execute that same command from VBA.

I've never used Putty's psftp.exe tool, only pscp.exe, so I can't offer help about how to construct your psftp.exe command line. One thing I noticed in Putty's documentation is that PSFTP (pscp.exe) can only work with a SSH-2 server --- if your target server supports only SSH-1, PSFTP will not work.

I think it would be worthwhile for you to review the Putty documentation at that link.