TicketMan
asked on
How to grab a file from a FTP-server with a ms-access vba module using SFTP - SSH File Transfer Protocol
Hi Experts,
My client is saving (.xls) data files on their own secure FTP-server for me to download and use in MS-Access. I currently use an ftp program "Filezilla" to manually login their server, grab the file and save it into a local folder. Every day, I process the data in those Excel files with MS-Access.
I would like to automate this daily sFTP-process from within MS-Access 2010 using VBA to automatically ftp 'GET' any file they place on their server for me. I do already have a VBA-module in MS-Access which can get files with FTP, but not for using SFTP-SSH File Transfer Protocol.
I think one approach is to use third party software like WinSCP maybe and trigger this (sFTP) software from the Windows command line with a little script file that can be rescheduled on a daily basis?
I use MS-Access 2010 on Windows XP Pro.
Can you help?
Many thanks!!!
My client is saving (.xls) data files on their own secure FTP-server for me to download and use in MS-Access. I currently use an ftp program "Filezilla" to manually login their server, grab the file and save it into a local folder. Every day, I process the data in those Excel files with MS-Access.
I would like to automate this daily sFTP-process from within MS-Access 2010 using VBA to automatically ftp 'GET' any file they place on their server for me. I do already have a VBA-module in MS-Access which can get files with FTP, but not for using SFTP-SSH File Transfer Protocol.
I think one approach is to use third party software like WinSCP maybe and trigger this (sFTP) software from the Windows command line with a little script file that can be rescheduled on a daily basis?
I use MS-Access 2010 on Windows XP Pro.
Can you help?
Many thanks!!!
ASKER
Thank you LukeChung-FMS, looking into it...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here by the way is the WaitWhileRunning() routine, which you will need for sure. This let's the batch file complete before execution will resume.
Holler if you have any questions.
Jim.
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
' Used for wait check.
Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400
Public Sub WaitWhileRunning(lngHWnd As Long)
Dim lngExitCode As Long
Dim lnghProcess As Long
10 lngExitCode = STILL_ACTIVE
20 lnghProcess = OpenProcess(PROCESS_QUERY_
30 If lnghProcess > 0 Then
40 Do While lngExitCode = STILL_ACTIVE
50 Call GetExitCodeProcess(lnghPro
60 DoEvents
70 Loop
80 End If
End Sub
Holler if you have any questions.
Jim.
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
' Used for wait check.
Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400
Public Sub WaitWhileRunning(lngHWnd As Long)
Dim lngExitCode As Long
Dim lnghProcess As Long
10 lngExitCode = STILL_ACTIVE
20 lnghProcess = OpenProcess(PROCESS_QUERY_
30 If lnghProcess > 0 Then
40 Do While lngExitCode = STILL_ACTIVE
50 Call GetExitCodeProcess(lnghPro
60 DoEvents
70 Loop
80 End If
End Sub
ASKER
Wow brilliant JDettman!!! How about that!
What is the strFTPSiteFingerprint parameter exactly, since it's not opitonal? I can login manually without it, I believe.
What is the strFTPSiteFingerprint parameter exactly, since it's not opitonal? I can login manually without it, I believe.
ASKER
Sorry, I had to look up first what a "FTP Site fingerprint" was and found this nice explanation on the web (source: http://www.wise-ftp.com/know-how/ftp_and_sftp.htm):
Before establishing a connection, the SFTP server sends an encrypted fingerprint of its public host keys to ensure that the SFTP connection will be exchanging data with the correct server. The first time the connection is established, this key is not yet known to the client program and must therefore be confirmed by the user before data is exchanged for the first time. Once you have established a connection to an FTP server and are sure that it is really the correct server, you should save the fingerprint information locally. This enables you to check the fingerprint information against the data you saved every time you establish a new connection to ensure that no one is between you and the server. Different servers issue fingerprints only once. They are generated by a server's private key.
Before establishing a connection, the SFTP server sends an encrypted fingerprint of its public host keys to ensure that the SFTP connection will be exchanging data with the correct server. The first time the connection is established, this key is not yet known to the client program and must therefore be confirmed by the user before data is exchanged for the first time. Once you have established a connection to an FTP server and are sure that it is really the correct server, you should save the fingerprint information locally. This enables you to check the fingerprint information against the data you saved every time you establish a new connection to ensure that no one is between you and the server. Different servers issue fingerprints only once. They are generated by a server's private key.
Since this is SSH, you may need a host key to be passed. This is in the format of:
ssh-rsa 1024 ad:d6:59:10:3f:72:cb:12:0d
See the WinSCP command line notes for -hostkey
Nice thing about this scripting method is that you can download WinSCP and try it manually from the command line to figure out what needs to be in the batch command file, then adjust the code accordingly.
With any FTP, I typically do the manual process first, then get a batch file that works, then modify the code.
The only part that's a bit ugly with this is determining if something suceeded or not (IsValidWinSCPFTP()) since each FTP client or type of FTP transfer does things a bit differently. To handle that, I have a series of the "IsValid..." procedures such as:
IsValidFTP() - Windows FTP client
IsValidWinSCPFTP - WinSCP Client
IsValidMoveItFreelyFTP - Move IT FTP client
along with the corresponding up/down file routines.
Jim.
ASKER
Fully with you, doing it manually first, then create batch command file accordingly. I can connect easily with FileZilla but for some reason not (yet) with WinSCP even with their GUI. Must be a setting I am missing..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Too bad Robo-FTP isn't free, but I am downloading and evaluating it now as I like the approach very much. At the same time, I am still trying to make that manual ftp connection with WinSCP...to test and customise the first approach.
Thanks. Great suggestions so far!
Thanks. Great suggestions so far!
ASKER
Having no luck connecting with the server using WinSCP (not to blame this software but myself) I am looking for another sftp program with command line capabilities.
I thought for a moment why not use Filezilla command line features but I think it won't do SFTP, only FTP...
To AlexPace: Is there a free alternative to Robo-FTP that can do your solution?
I thought for a moment why not use Filezilla command line features but I think it won't do SFTP, only FTP...
To AlexPace: Is there a free alternative to Robo-FTP that can do your solution?
<<Having no luck connecting with the server using WinSCP (not to blame this software but myself) I am looking for another sftp program with command line capabilities. >>
Well there are a couple of different flavors of FTP; what is it exactly your running into?
Another that I use is MoveItFreely, which does FTPs (SSL) connections:
http://www.ipswitchft.com/products/moveitFreely/index.aspx
Are you in control of the server that your getting the data from? If not, do you know what FTP server software they are using? That might give you a clue as to what the setups are that their capable of.
Jim.
Well there are a couple of different flavors of FTP; what is it exactly your running into?
Another that I use is MoveItFreely, which does FTPs (SSL) connections:
http://www.ipswitchft.com/products/moveitFreely/index.aspx
Are you in control of the server that your getting the data from? If not, do you know what FTP server software they are using? That might give you a clue as to what the setups are that their capable of.
Jim.
As far as I know WinSCP is the most scriptable (?) of the free SFTP clients.
I feel wierd saying this, any maybe I shouldn't say it, but if you spend all day figuring out how to do something for free, it isn't really free when you consider the value of your time.
I feel wierd saying this, any maybe I shouldn't say it, but if you spend all day figuring out how to do something for free, it isn't really free when you consider the value of your time.
ASKER
Point taken AlexPace.
I have no control of the server but I can ask them what software they use (weekend here now...)
I have no control of the server but I can ask them what software they use (weekend here now...)
ASKER
Many thanks for your very valuable help!
One of its categories is FTP file upload and download routines. More info here: http://www.fmsinc.com/MicrosoftAccess/modules/code/index.html
Hope this helps.