Hello: I am trying to develop a module within my Access database that once triggered will connect to an SFTP site and download a specific file onto my local drive. I've tried a number of different solutions I've found throughout the forum but can't find anything that works. I am using Access 2010 and I have WinSCP installed to manually connect to the SFTP site. Any help is greatly appreciated. Thanks.
Connect to WinSCP SFTP Via Access VBA
- Thread starter davidb88
- Start date
Thanks so much. This solution seems to work. I actually saw this earlier and it didn't work but after I re-looked at it from your post I noticed that within WinSCP there is the ability to generate a URL which I used and it worked. For those looking at this in the future, make sure to include the host key from the WinSCP generated URL to get it to work.
The one open question I had though was in the example, it is picking up everything within the SFTP folder. Any idea what the syntax would be if I want a specific file name? Thanks again.
The one open question I had though was in the example, it is picking up everything within the SFTP folder. Any idea what the syntax would be if I want a specific file name? Thanks again.
- Local time
- Today, 10:00
- Joined
- Jan 23, 2006
- Messages
- 15,465
David,
My guess is you would change this line
to the specific file name you wanted.
Suppose the file you wanted was Myfile.dox, then I'd try
I don't used FTP or SFTP but if that doesn't work, could you post the code you had success with and readers will offer suggestions.
Good luck.
My guess is you would change this line
Code:
get *.* c:\test\from\to the specific file name you wanted.
Suppose the file you wanted was Myfile.dox, then I'd try
Code:
get Myfile.dox c:\test\from\I don't used FTP or SFTP but if that doesn't work, could you post the code you had success with and readers will offer suggestions.
Good luck.
This is the solution that worked for me. Taken directly from the thread linked above by jdraw.
Paste this into your module within Access.
Then in a text file paste the following. Remember to get the URL directly from WinSCP.
Paste this into your module within Access.
Code:
Sub SFTPGet()
Dim strQuote As String
strQuote = Chr(34)
Dim strSFTPDir As String
strSFTPDir = "c:\program files (x86)\winscp\"
Dim strCommand As String
strCommand = "/script=c:\WinSCPGet.txt" 'change the file name/path specific to your computer
Call Shell(strSFTPDir & "winscp.com " & strQuote & strCommand & strQuote, vbNormalNoFocus)
End SubThen in a text file paste the following. Remember to get the URL directly from WinSCP.
Code:
# Connect to the host and login using password
open sftp://[Username]:[password];fingerprint=[SSH Host Key]@[SFTP URL]
# Change the remote directory
cd [File path where the desired file is held on the SFTP site]
# get all the files in the remote directory and download them to a specific local directory
get Test.xlsx c:\ 'change the file name accordingly
# remove the files from the remote direcory
# rm *.*
# Close and terminate the session
exit