We help IT Professionals succeed at work.

How to grab a file from a FTP-server with a ms-access vba module using SFTP - SSH File Transfer Protocol

Medium Priority
6,439 Views
Last Modified: 2012-05-12
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!!!
Comment
Watch Question

Luke ChungPresident
CERTIFIED EXPERT

Commented:
We offer a commercial program, Total Visual SourceBook, that includes tons of royalty-free Access/VBA source code.

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.

Author

Commented:
Thank you LukeChung-FMS, looking into it...
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
<<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?>>

  That's the ticket!  I've attached some code that shows an upload (download is similar).  Note this code won't run if you just drop it in as it relies on a number of routines, but it shows you how it's done.

Jim.
Function FTPUploadFileWithWinSCP(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUsername As String, strPassword As String, strFTPSiteFingerprint As String, Optional strTransferType As String) As Boolean

          ' Procedure to upload file to FTP site.
          ' Uses WinSCP Client to upload File, which allows a SFTP transfer.
          ' Sends e-mail to ITALERT if upload fails and returns false.

          Const RoutineName = "FTPUploadFileWithWinSCP"
          Const Version = "1.1"

          Dim strFTPCommandFile As String
          Dim strFTPScriptFile As String
          Dim strFTPLogfile As String

          Dim lngHWnd As Long
          Dim intFileNum As Integer
          Dim strMailMessage As String

          Dim oOCS_SendMail As New OCS_SendMail

10        On Error GoTo FTPUploadFileWithWinSCP_Error

          ' Generate file names
20        strFTPCommandFile = "\FTP_" & AppShortName() & ".bat"
30        strFTPScriptFile = "\FTP_" & AppShortName() & ".txt"
40        strFTPLogfile = "\FTP_" & AppShortName() & ".Log"

          ' Write script file
50        intFileNum = FreeFile
60        Open strFTPScriptFile For Output As #intFileNum
70        Print #intFileNum, "option batch on"
80        Print #intFileNum, "option confirm off"

90        Print #intFileNum, "open sftp://" & strUsername & ":" & strPassword & "@" & strFTPSiteName & " -hostkey=" & Chr$(34) & strFTPSiteFingerprint & Chr$(34)
120       Print #intFileNum, "option transfer " & IIf(strTransferType = "B", "binary", "ascii")
130       Print #intFileNum, "put " & Chr$(34) & strLocalFileName & Chr$(34) & " " & strFTPFilename
140       Print #intFileNum, "Close"

150       Print #intFileNum, "Exit"
160       Close #intFileNum

          ' Write command file
170       intFileNum = FreeFile

180       Open strFTPCommandFile For Output As #intFileNum
190       Print #intFileNum, Chr$(34) & "C:\Program Files\WinSCP\winscp.com" & Chr$(34) & "/console /script=" & strFTPScriptFile & " > " & strFTPLogfile
200       Close #intFileNum

          ' Execute
210       lngHWnd = Shell(strFTPCommandFile, vbHide)
220       WaitWhileRunning (lngHWnd)

          ' Check log file
230       If IsValidWinSCPFTP(strFTPLogfile, strFTPFilename) Then
240           FTPUploadFileWithWinSCP = True
250       Else
260           If DebugMode() = True Then
270   Stop
280               FTPUploadFileWithWinSCP = False
290           Else
300               oOCS_SendMail.SetParams "ITALERT", ".", "."
310               oOCS_SendMail.Subject = "WinSCP FTP Upload failed."
320               strMailMessage = "The file: " & strLocalFileName & " did not upload." & vbCrLf
330               strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
340               strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
350               oOCS_SendMail.Message = strMailMessage
360               oOCS_SendMail.Attachment = strFTPCommandFile & ";" & strFTPScriptFile & ";" & strFTPLogfile
370               oOCS_SendMail.Send
380               FTPUploadFileWithWinSCP = False
390           End If
400       End If

FTPUploadFileWithWinSCP_Exit:
410       On Error Resume Next

420       If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
430       If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
440       If Dir(strFTPLogfile) <> "" Then Kill strFTPLogfile

450       Close #intFileNum

460       Exit Function

FTPUploadFileWithWinSCP_Error:
470       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
480       FTPUploadFileWithWinSCP = False
490       Resume FTPUploadFileWithWinSCP_Exit

End Function


Function IsValidWinSCPFTP(strFTPLogfile, strFileName) As Boolean

          ' Checks log file to see if last FTP operation OK.
          ' Looks for file name uploaded and "100%"
          Const RoutineName = "IsValidWinSCPFTP"
          Const Version = "1.2"

          Dim intFileNum As Integer
          Dim lsLine As String

10        On Error GoTo IsValidWinSCPFTP_Error

20        IsValidWinSCPFTP = False
30        intFileNum = FreeFile
40        Open strFTPLogfile For Input As #intFileNum

50        Do While Not EOF(intFileNum) And IsValidWinSCPFTP = False
60            Line Input #intFileNum, lsLine
70            If InStr(lsLine, strFileName) > 0 And InStr(lsLine, "100%") > 0 Then IsValidWinSCPFTP = True
80        Loop

IsValidWinSCPFTP_Exit:
90        On Error Resume Next

100       Close #intFileNum

110       Exit Function

IsValidWinSCPFTP_Error:
120       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
130       Resume IsValidWinSCPFTP_Exit

End Function

Open in new window

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
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_INFORMATION, False, lngHWnd)

30        If lnghProcess > 0 Then
40            Do While lngExitCode = STILL_ACTIVE
50                Call GetExitCodeProcess(lnghProcess, lngExitCode)
60                DoEvents
70            Loop
80        End If

End Sub

Author

Commented:
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.

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

  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:b6:ef:8b:0f:5a:84:fb

  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.

Author

Commented:
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..
Commented:
If you used Robo-FTP you could install a command script as a Windows Service so it is always running.  Here is a simple STFP script that downloads all the .xls files every 5 mins.  The files are removed from the server only if the downloads are successful.

Because this script is always bringing down any new xls files, all your MS Access code has to do is check the local folder and process anything it finds.
WORKINGDIR "c:\local\destination\folder"
:top
CRON "@every5min"
FTPLOGON "ftp.mydomain.com" /user="UserID" /pw="Secret" /servertype=SFTP /trust=allow
RCVFILE "*.xls" /delete
FTPLOGOFF
GOTO top

Open in new window

In this script the client just accepts whatever SSH keys are presented by the server.  If you wish higher security, you can remove /trust=all from the FTPLOGON command line after the first sucessful connection.  Subsequent connections will fail if the key fingerprint does not match the locally cached copy.  

Author

Commented:
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!

Author

Commented:
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?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<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.

Commented:
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.  

Author

Commented:
Point taken AlexPace.

I have no control of the server but I can ask them what software they use (weekend here now...)

Author

Commented:
Many thanks for your very valuable help!