Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

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.

    VB:
    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
    450

    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.

    VB:
    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
    450

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


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, 19:40
  2. Replies: 1
    Last Post: June 21st, 2012, 11:21
  3. Download large file results in MHT file
    By doasidont in forum EXCEL HELP
    Replies: 3
    Last Post: December 2nd, 2011, 22:45
  4. Import Newest Text File
    By snowy123 in forum EXCEL HELP
    Replies: 2
    Last Post: July 30th, 2007, 21:55
  5. finding newest timestamped file
    By steve_bris in forum EXCEL HELP
    Replies: 1
    Last Post: March 24th, 2005, 17: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