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.

 

[Legacy 98055]

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

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</pre>


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