Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

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

  1. #1
    Join Date
    15th December 2011
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st November 2012
    Posts
    731

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th December 2011
    Posts
    5

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st November 2012
    Posts
    731

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th December 2011
    Posts
    5

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

    Thanks John_w. The code you provided works perfectly.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th June 2014
    Posts
    1

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    4,812

    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.

  8. #8
    Join Date
    21st November 2012
    Posts
    731

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

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. identify the newest file.
    By wantubididoo99 in forum EXCEL HELP
    Replies: 1
    Last Post: November 20th, 2012, 20:40
  2. Replies: 1
    Last Post: June 21st, 2012, 12:21
  3. Download large file results in MHT file
    By doasidont in forum EXCEL HELP
    Replies: 3
    Last Post: December 2nd, 2011, 23:45
  4. Import Newest Text File
    By snowy123 in forum EXCEL HELP
    Replies: 2
    Last Post: July 30th, 2007, 22:55
  5. finding newest timestamped file
    By steve_bris in forum EXCEL HELP
    Replies: 1
    Last Post: March 24th, 2005, 18:03

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno