Select latest file on ftp server from access 1

[DijaBug]

I am trying to find a way to get the latest file name (just the name not the file) from an FTP server from within microsoft access. Most days there is just 1 file for that specific day, but then there are other days that there are multiple files marked by a 000 or 001. I just need something to return the name of the latest file.

Example of what the files names would look like and in this example on this day I would want the "6-1-06-002.txt"

6-1-06-000.txt
6-1-06-001.txt
6-1-06-002.txt

Any suggestions are welcome!

 

[JerryKlmns]

Function FindLatestFile(Search_Folder As String) As String

Dim fso As Object
Dim fld As Object
Dim fls As Object
Dim fl As Object
Dim rst As ADODB.Recordset
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(Search_Folder)
    Set fls = folder.files
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        .Fields.Append "FileName", adVarChar, 252
        .Open
        For Each fl In fls
            If fso.GetExtensionName (Search_Folder & "\" & fl) & "" = "Text" Then 
            .AddNew 
            .Fields("FileName") = fl.Name
        Next
        .UpdateBatch
        .Sort = "FileName"
        If .EOF = True Or .BOF = True Then
            FindLatestFile = "NO FILE"
        Else
            .MoveLast
            FindLatestFile = Search_Folder & "\" & .Fields("FileName")
        End If
        .Close
    End With
    Set rst = Nothing

End Function

 

[DijaBug]

While this would work if the files were allready in a folder, the problem is that I need to get the name of a file that is on a FTP server. the files tend to be large, and download extremly slowly so I dont really want to download multiples of them if I dont have to.

 

[CautionMP]

DijaBug,
What method are you using to get the file in Access?

If you're using WININET.dll you can use [tt]FTPFindFirstFile[/tt] in conjunction with [tt]InternetFindNextFile[/tt] to get the list of files.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[DijaBug]

Right now, I just write ftp commands to a txt file and then open a command prompt to issue the ftp commands, because before now, all the filenames were known and constant (entire process is automated of course)

Never used the WININET.dll, Ill try and figure out how to use that maybe it wlil get me a little farther in this.

 

[CautionMP]

DijaBug,
This may be of some interest to you, it covers the basics of using the WININET.dll:
Microsoft: Access Modules (VBA Coding) > FAQs: How do I FTP files to a remote server in MS Access

CMP

(GMT-07:00) Mountain Time (US & Canada)