SFTP upload with VBA

Question

i need to be able to SFTP though VBA. I have an Access program that pulls data, manipulates it and now i have to find a way to upload the excel 07 file through SFTP.

i've been looking on the net for days and can't find anything. I saw a similar topic here http://stackoverflow.com/questions/202197/how-to-use-sftp-from-within-an-ms-access-database-module, and i'd love to find Mat Nadrofsky, because it seemed like he has a solution, i just cant understand any of it)))))))))))

so if someone can explain what that solution was about or has a different solution - i'd really appreciate it thank you

Answer 1

I have done it the following way in Access 97:

1. Buy a SFTP client that has an OCX that is available to MS Access
2. Write VBA code to use the SFTP control

In one particular case, there was no OCX - only executable - we had to do a batch file for that one.

Answer 2

You would need some SFTP ActiveX Control that works in Access. I know that our SFTP control is used by some customers in VBA and in Access in particular.

Answer 3

In the previous SO answer you linked, Mat Nadrofsky used an sftp command line client. In this example my sftp client is pscp.exe. That client is part of the PuTTY tools: PuTTY Download Page

I want to build and run a command like this to copy sample.txt to my home directory on the remote machine:

"C:\Program Files\PuTTY\pscp.exe" -sftp -l hans -pw changeme C:\Access\sample.txt 192.168.1.6:/home/hans/

So this procedure will build and run that command string.

Public Sub SftpPut()
    Const cstrSftp As String = """C:\Program Files\PuTTY\pscp.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 = "hans"
    pPass = "changeme"
    pHost = "192.168.1.6"
    pFile = "C:\Access\sample.txt"
    pRemotePath = "/home/hans/"

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

You may prefer ShellAndWait instead of Shell, as David Fenton suggested in a comment on the previous answer.