loop through a directory and find the most recently created file

[mrpknd57]

Hi, I am new to Access/VBA can anyone help?

I have a form with a button that runs a macro. I have been using the TransferText action within the macro to import a text file, from a specific directory into an Access table. The macro then runs a couple of queries to re-format the data and prints a report. It then exports the data back into a text file with a new name, to a different directory and deletes the table created in Access. This works very well if the name of the text file is the same each time, but unfortunately the name of text file changes each time it is created. Unfortunately I have no control over the naming of the files.

An example of file names in the directory is:-

00100transfer.txt,
00101transfer.txt,
00102transfer.txt etc…

I need to be able to tell the macro to pick up the most recently created file in the directory. The sequence number part of the file is incremental, so the most recent file will always have the highest sequence number.

I really don’t know where to start to so any help would be much appreciated.

Thanks in advance.

[Rabbit]

Here's code I've used before to list files. You can chop it up and modify it to do what you need.

Function Check()
With Application.FileSearch
    Dim length As Integer
    length = Len(Application.CurrentProject.path) + 10
    .NewSearch
    .LookIn = Application.CurrentProject.path & "\Exports\"
    .SearchSubFolders = False
    .FileName = "CDCR " & Me.OpenArgs & " Export"
    .MatchTextExactly = False
    .FileType = msoFileTypeDatabases
 
    If .Execute() > 0 Then
        Dim i As Integer
        Dim Files As String
        Files = "'" & Mid(.FoundFiles(1), length) & "'"
        For i = 2 To .FoundFiles.Count
            Files = Files & ";'" & Mid(.FoundFiles(i), length) & "'"
        Next i
        FileList.RowSource = Files
    Else
        FileList.RowSource = "'No Files Found.'"
    End If
End With
End Function