10

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

Improve this question

3 Answers 3

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

first of all, thank you so much. i was able to find the guy from the link post and he told me about putty and i was able to get it to work with putty and ShellAndWait. your VBA code is much better than mine though))))))))))))) One problem, it works on a company laptop from home but not from work. i'm sure something is blovking it, and i asked IT to see what it is, but it's been days, he tried a few things and i have a feeling he will come back and say "Upload by hand". so i'm not sure where i am now, if it's being blocked because of putty or what
i just noticed something, you're using pscp.exe. i was using psftp.exe, also from Putty. Is there a chance that pscp.exe won't be blocked and psftp.exe is? it seems unlikely to me, but i'm desperate to have the whole thing automated and ready to try anything. it just seems wrong to me to have a program that's automated down to a click and then sftp by hand. i hope someone understands.
Are you able to upload with sftp by any method from your workplace network? If not, my guess is the sftp port is blocked there. I doubt pscp.exe vs. psftp.exe would make a difference, but you can try switching anyway. What response do you see in the shell window when it doesn't work?
i'm able to sftp by hand, meaning just going to their site in internet explorer, clicking upload and so on. if it helps when i type "telnet 44.44.44.44 22" at the command prompt i get nothing, everything disappears and the window is just black, so it's not connecting. and here's everything i get after typing psftp connection string at the command prompt Looking up host "sftp.ffffff.com" Connecting to 44.44.44.44 port 22 Network error: Software caused connection abort Fatal: Network error: Software caused connection abort and thank you so much for your time
I'm not sure what connecting with IE tells you ... perhaps the sftp port is proxied for http. From Windows when I telnet 192.168.1.6 22, I get a response SSH-2.0-OpenSSH_4.3 from the Linux server. But you get nothing on your workplace network. What telnet response do you get from home, where psftp.exe works?
|
0

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.

Improve this answer

1 Comment

i saw your site before and it looks like it'd work for me, except for i need this done to save me a few steps that i can otherwise do by hand and my company will never pay for that))))) thanks anyway
-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.

Improve this answer

4 Comments

thank you, as i said belowe, my company won't pay for that, so i'm trying to see if there's anything i can do by code. it seemd like on the link i'm giving in my post they found a solution, i just don't have enough knowledge to pick up what they meant
You will probably end up spending more time on building something yourself than just buy an off the shelf component. Build-vs-Buy
true, and in some cases it's worth it to buy it, but in the process i also get to learn a lot, and second of all, in this case i don't have a choice. i need to automate one step that i can otherwise do by hand. my company will never pay for that and i'd totally understand. anyhow, i got it to work.
sorry, second thought, i got it to work by using PUTTY, so i didn't build anything myself, so you're right. it's just that PUTTY is free. you are right in this case 100% because i could never build anything like this on my own. i was hoping to find some code on the net that i can customize.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.