Automatically select/open most recent file

[flightjock]

We have to use data in a worksheet from an excel file that somebody else puts in a fixed directory on our network every day. Each day this source file has another name with the date included in the name. E.g. today's file would be called DRAAF20020426.xls and tomorrow's file would be called DRAAF20020427. The older files are not deleted.
Would it be possible to use VBA to automatically select the most recent file in this source directory and open it?

Any help or suggestion or hopefully solution would be very much appreciated.

[Tom Schreiner]

Hi
Tested this.
Seems to work.
The only thing you should
need to do is add the correct path.

Sub FindLatestFile()

Dim i

Dim DateOne As Date

Dim DateTwo As Date

Dim YourPath As String

Dim YourFile As String



YourPath = "C:YourPath" 'change this to the correct path



With Application.FileSearch

    .NewSearch

    .LookIn = YourPath

    If .Execute() > 0 Then

        For i = 1 To .FoundFiles.Count

        If IsDate(Left(Right(.FoundFiles(i), 8), 2) & "/" & _

                  Left(Right(.FoundFiles(i), 6), 2) & "/" & _

                  Left(Right(.FoundFiles(i), 12), 4)) Then

            DateTwo = Left(Right(.FoundFiles(i), 8), 2) & "/" & _

                  Left(Right(.FoundFiles(i), 6), 2) & "/" & _

                  Left(Right(.FoundFiles(i), 12), 4)

            If DateTwo > DateOne Then

                DateOne = DateTwo

                YourFile = .FoundFiles(i)

            End If

        End If

        Next i

    Else

        MsgBox "There were no files found."

    End If

End With

Workbooks.Open YourFile

End Sub

Tom


[ This Message was edited by: TsTom on 2002-04-26 05:53 ]

[flightjock]

Thanks,
However it does not work completely correct. It does open a file, but not the most recent one. I noticed that in the code you compare datetwo with dateone. However, I find no place in the code were dateone is calculated. Could this be the problem?

Tony

[flightjock]

Tom,
I found the problem. It has to to with date formatting. We use MM/DD/YYYY where you probably had MM/DD/YYYY. Now it works perfectly.

Thanks a lot for the fast solution
Tony