Announcement

Collapse
No announcement yet.

How to use VBA FTP to download newest file in directory

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to use VBA FTP to download newest file in directory



    Hello Everyone,

    I've successfully written code to download a file from an FTP server. The problem is that there are several files out there with similar names. For example: Test01012013.csv and Test01022013.csv. I'm trying to find a way to find and download the newest file based on created date. I believe I may need to use InternetFindNextFile, but not sure how to incorporate that into the code and use it to find the newest file. Here is a copy of my code.

    Code:
    Sub DownloadTransformUploadFTP()
    
    Dim lngINetConn
    Dim lngINet
    Dim blnRC As Boolean
    Dim UserName As String
    Dim Password As String
    Dim serverName As String
    Const ASCII_TRANSFER = 1
    Const BINARY_TRANSFER = 2
    Dim fData As WIN32_FIND_DATA
    Dim Check1
    Dim Check2
    
        serverName = "testftp.com"
        UserName = "test"
        Password = "test"
        localFile = "C:\Test.xlsx"
        hostFile = "/outgoing/Test.xlsx"
    
    blnRC = False
    lngINet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
    If lngINet > 0 Then
    lngINetConn = InternetConnect(lngINet, serverName, 0, UserName, Password, 1, 0, 0)
    If lngINetConn > 0 Then
    
    Check1 = FtpSetCurrentDirectory(lngINetConn, "/outgoing" + vbNullString)
    
    fData.cFileName = String(MAX_PATH, 0)
    
    Check2 = FtpFindFirstFile(lngINetConn, "Test.xlsx", fData, 0, 0)
    
    blnRC = FtpGetFile(lngINetConn, "/outgoing/" & fData.cFileName, localFile, 0, 0, 1, 0)
    InternetCloseHandle lngINetConn
    End If
    InternetCloseHandle lngINet
    End If
    
    Workbooks.Open Filename:=localFile

  • #2
    Re: How to use VBA FTP to download newest file in directory

    It's easier to help you if you post the whole code, including the Windows API function declarations, etc.

    Originally posted by darian60 View Post
    For example: Test01012013.csv and Test01022013.csv. I'm trying to find a way to find and download the newest file based on created date.
    Do you mean the internal file creation date, or the date string in the file name? If the latter, is the date string ddmmyyyy or mmddyyyy?

    Comment


    • #3
      Re: How to use VBA FTP to download newest file in directory

      Thanks for the reply. Here is the whole code:

      I mean the internal file creation date.

      Code:
      Const MAX_PATH = 260
      
      Private Type FILETIME
      dwLowDateTime As Long
      dwHighDateTime As Long
      End Type
      
      Private Type WIN32_FIND_DATA
      dwFileAttributes As Long
      ftCreationTime As FILETIME
      ftLastAccessTime As FILETIME
      ftLastWriteTime As FILETIME
      nFileSizeHigh As Long
      nFileSizeLow As Long
      dwReserved0 As Long
      dwReserved1 As Long
      cFileName As String * MAX_PATH
      cAlternate As String * 14
      End Type
      
       Private Declare Function InternetOpen _
         Lib "wininet.dll" _
           Alias "InternetOpenA" _
             (ByVal sAgent As String, _
              ByVal lAccessType As Long, _
              ByVal sProxyName As String, _
              ByVal sProxyBypass As String, _
              ByVal lFlags As Long) As Long
      
       Private Declare Function InternetConnect _
         Lib "wininet.dll" _
           Alias "InternetConnectA" _
             (ByVal hInternetSession As Long, _
              ByVal sServerName As String, _
              ByVal nServerPort As Integer, _
              ByVal sUsername As String, _
              ByVal sPassword As String, _
              ByVal lService As Long, _
              ByVal lFlags As Long, _
              ByVal lContext As Long) As Long
      
       Private Declare Function FtpGetFile _
         Lib "wininet.dll" _
           Alias "FtpGetFileA" _
             (ByVal hFtpSession As Long, _
              ByVal lpszRemoteFile As String, _
              ByVal lpszNewFile As String, _
              ByVal fFailIfExists As Boolean, _
              ByVal dwFlagsAndAttributes As Long, _
              ByVal dwFlags As Long, _
              ByVal dwContext As Long) As Boolean
      
       Private Declare Function FtpPutFile _
         Lib "wininet.dll" _
           Alias "FtpPutFileA" _
             (ByVal hFtpSession As Long, _
              ByVal lpszLocalFile As String, _
              ByVal lpszRemoteFile As String, _
              ByVal dwFlags As Long, _
              ByVal dwContext As Long) As Boolean
              
      Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias _
       "FtpSetCurrentDirectoryA" (ByVal hFtpSession As Long, ByVal lpszDirectory As String) _
       As Boolean
       
      Private Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
          (ByVal hFtpSession As Long, ByVal lpszSearchFile As String, _
          lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
          ByVal dwContent As Long) As Long
      
      
       Private Declare Function InternetCloseHandle _
         Lib "wininet.dll" _
           (ByVal hInet As Long) As Integer
           
      Sub DownloadTransformUploadFTP()
      
      Dim lngINetConn
      Dim lngINet
      Dim blnRC As Boolean
      Dim UserName As String
      Dim Password As String
      Dim serverName As String
      Const ASCII_TRANSFER = 1
      Const BINARY_TRANSFER = 2
      Dim fData As WIN32_FIND_DATA
      Dim Check1
      Dim Check2
      
          serverName = "test.com"
          UserName = "test"
          Password = "test"
          localFile = "C:\Test.xlsx"
          hostFile = "/outgoing/Test.xlsx"
      
      blnRC = False
      lngINet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
      If lngINet > 0 Then
      lngINetConn = InternetConnect(lngINet, serverName, 0, UserName, Password, 1, 0, 0)
      If lngINetConn > 0 Then
      
      Check1 = FtpSetCurrentDirectory(lngINetConn, "/outgoing" + vbNullString)
      
      fData.cFileName = String(MAX_PATH, 0)
      
      Check2 = FtpFindFirstFile(lngINetConn, "Test.xlsx", fData, 0, 0)
      
      blnRC = FtpGetFile(lngINetConn, "/outgoing/" & fData.cFileName, localFile, 0, 0, 1, 0)
      InternetCloseHandle lngINetConn
      End If
      InternetCloseHandle lngINet
      End If
      
      Workbooks.Open Filename:=localFile

      Comment


      • #4
        Re: How to use VBA FTP to download newest file in directory

        This works for me. You just need to change the user-defined settings shown in the code to suit your ftp server and local folder path.
        Code:
        Option Explicit
        
        Const MAX_PATH = 260
         
        Private Type WIN32_FIND_DATA
            dwFileAttributes As Long
            ftCreationTime As Currency          'low & high 32 bits as 64-bit data type
            ftLastAccessTime As Currency
            ftLastWriteTime As Currency
            nFileSizeHigh As Long
            nFileSizeLow As Long
            dwReserved0 As Long
            dwReserved1 As Long
            cFileName As String * MAX_PATH
            cAlternate As String * 14
        End Type
         
        
        Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
            (ByVal sAgent As String, _
            ByVal lAccessType As Long, _
            ByVal sProxyName As String, _
            ByVal sProxyBypass As String, _
            ByVal lFlags As Long) As Long
        
        Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
            (ByVal hInternetSession As Long, _
            ByVal sServerName As String, _
            ByVal nServerPort As Integer, _
            ByVal sUsername As String, _
            ByVal sPassword As String, _
            ByVal lService As Long, _
            ByVal lFlags As Long, _
            ByVal lContext As Long) As Long
         
        Private Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
            (ByVal hFtpSession As Long, _
            ByVal lpszRemoteFile As String, _
            ByVal lpszNewFile As String, _
            ByVal fFailIfExists As Boolean, _
            ByVal dwFlagsAndAttributes As Long, _
            ByVal dwFlags As Long, _
            ByVal dwContext As Long) As Boolean
         
        Private Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
            (ByVal hFtpSession As Long, _
            ByVal lpszLocalFile As String, _
            ByVal lpszRemoteFile As String, _
            ByVal dwFlags As Long, _
            ByVal dwContext As Long) As Boolean
         
        Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
            (ByVal hFtpSession As Long, _
            ByVal lpszDirectory As String) As Boolean
         
        Private Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
            (ByVal hFtpSession As Long, _
            ByVal lpszSearchFile As String, _
            lpFindFileData As WIN32_FIND_DATA, _
            ByVal dwFlags As Long, _
            ByVal dwContent As Long) As Long
         
        Private Declare Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
            (ByVal hFind As Long, _
            lpFindFileData As WIN32_FIND_DATA) As Long
        
        Private Declare Function InternetCloseHandle Lib "wininet.dll" _
            (ByVal hInet As Long) As Integer
         
         
        Public Sub Ftp_Download_Newest_File()
             
            Dim hOpen As Long, hConn As Long, hFind As Long
            Dim ret As Long
            Dim hostName As String, port As Long, username As String, password As String
            Dim localFolder As String
            Dim remoteDirectory As String, remoteMatchFiles As String
            Dim ftpMode As Long
            Dim fileFind As WIN32_FIND_DATA
            Dim newestFileTime As Currency
            Dim newestFileName As String
            
            '========== User-defined settings ==========    
            
            localFolder = "C:\Path\To\Local\Folder\"
            hostName = "ftp.server.host.name"
            port = 21
            username = "YourUsername"
            password = "YourPassword"
            remoteDirectory = "/remote/server/directory/"
            remoteMatchFiles = "*.csv"
            '===========================================
            
            ftpMode = 0                         'active mode FTP
            'ftpMode = INTERNET_FLAG_PASSIVE    'passive mode FTP
            
            ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
            hOpen = ret
            
            If ret > 0 Then
                ret = InternetConnect(hOpen, hostName, port, username, password, INTERNET_SERVICE_FTP, ftpMode, 0)
                hConn = ret
            End If
            
            If ret > 0 Then
                ret = FtpSetCurrentDirectory(hConn, remoteDirectory)
            End If
            
            If ret > 0 Then
                
                'Find first matching file
                
                fileFind.cFileName = String(MAX_PATH, vbNullChar)
                ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
                hFind = ret
            
                While ret > 0
                    Debug.Print TrimNulls(fileFind.cFileName)
                    
                    'Is this file newer than the newest file seen so far?
                    
                    If fileFind.ftLastWriteTime > newestFileTime Then
                        newestFileTime = fileFind.ftLastWriteTime
                        newestFileName = TrimNulls(fileFind.cFileName)
                    End If
                    
                    'Find next matching file
                    
                    fileFind.cFileName = String(MAX_PATH, vbNullChar)
                    ret = InternetFindNextFile(hFind, fileFind)
                
                Wend
                
                Debug.Print "Newest "; newestFileName
                
                'Download the newest file to local folder
                
                ret = FtpGetFile(hConn, newestFileName, localFolder & newestFileName, False, 0, FTP_TRANSFER_TYPE_BINARY Or INTERNET_FLAG_RELOAD, 0)
                
                If ret = 0 Then
                    Debug.Print "FtpGetFile error "; Err.LastDllError
                End If
            
            End If
            
            'Release handles
            
            InternetCloseHandle hFind
            InternetCloseHandle hConn
            InternetCloseHandle hOpen
            
        End Sub
        
        
        Private Function TrimNulls(buffer As String) As String
            TrimNulls = Left(buffer, InStr(buffer, vbNullChar) - 1)
        End Function

        Comment


        • #5
          Re: How to use VBA FTP to download newest file in directory

          Thanks John_w. The code you provided works perfectly.

          Comment


          • #6
            Re: How to use VBA FTP to download newest file in directory

            Hello,
            I tried to execute the last posted code in an excel Macro (MS Excel 2010), but the system cannot recognise the below values:

            INTERNET_SERVICE_FTP
            INTERNET_FLAG_RELOAD
            INTERNET_FLAG_NO_CACHE_WRITE
            FTP_TRANSFER_TYPE_BINARY
            INTERNET_FLAG_RELOAD

            Could you please advise how to define the relevant missing info?

            Comment


            • #7
              Re: How to use VBA FTP to download newest file in directory

              nickark
              welcome to the forum
              Please do not post questions in other peoples threads. This is known as thread hijacking. Please start your own thread for this question with a meaningful thread title. You can create a link back to this thread if you think it will be useful.
              If the solution helped please donate to RSPCA

              Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

              Comment


              • #8


                Re: How to use VBA FTP to download newest file in directory

                Originally posted by nickark View Post
                Hello,
                I tried to execute the last posted code in an excel Macro (MS Excel 2010), but the system cannot recognise the below values:

                INTERNET_SERVICE_FTP
                INTERNET_FLAG_RELOAD
                INTERNET_FLAG_NO_CACHE_WRITE
                FTP_TRANSFER_TYPE_BINARY
                INTERNET_FLAG_RELOAD

                Could you please advise how to define the relevant missing info?
                Search for "const INTERNET_SERVICE_FTP" and the rest and you should find the definitions.

                Comment

                Working...
                X