Identifying The Newest File In A Directory
The VBA function listed below (two versions) returns the name of the most recent file in a directory. The function takes two arguments:
- Directory: The full path of the directory (String). For example, "c:\files\excel\"
- FileSpec: The file specification (String). For example, "*.xls" for Excel workbooks, or "*.*" for all files.
If the directory does not exist, or if it contains no matching files, the function returns an empty string.
Method 1: Using the Dir function
This function uses VBA's Dir function to get the file names. Use this function for maximum compatibility with older versions of Excel.
Function NewestFile(Directory, FileSpec) ' Returns the name of the most recent file in a Directory ' That matches the FileSpec (e.g., "*.xls"). ' Returns an empty string if the directory does not exist or ' it contains no matching files Dim FileName As String Dim MostRecentFile As String Dim MostRecentDate As Date If Right(Directory, 1) <> "\" Then Directory = Directory & "\" FileName = Dir(Directory & FileSpec, 0) If FileName <> "" Then MostRecentFile = FileName MostRecentDate = FileDateTime(Directory & FileName) Do While FileName <> "" If FileDateTime(Directory & FileName) > MostRecentDate Then MostRecentFile = FileName MostRecentDate = FileDateTime(Directory & FileName) End If FileName = Dir Loop End If NewestFile = MostRecentFile End Function
Method 2: Using the FileSearch object
This function uses the FileSearch object, which is not supported in all versions of Excel. Unlike the previous version of the function, this one returns the full path as well as the file name.
Also, be aware that the FileSearch object was removed, beginning with Office 2007.
Function NewestFile(Directory, FileSpec) ' Returns the full path and name of the most recent file in a Directory ' That matches the FileSpec (e.g., "*.xls"). ' Returns an empty string if the directory does not exist or ' it contains no matching files Dim NumFound As Long NewestFile = "" With Application.FileSearch .NewSearch .LookIn = Directory .FileName = FileSpec NumFound = .Execute(SortBy:=msoSortByLastModified, _ SortOrder:=msoSortOrderDescending) If NumFound > 0 Then NewestFile = .FoundFiles(1) End With End Function
This function can be called from a VBA procedure, or used in a worksheet formula. The statement below displays the name of the most recent Excel file in c:\myfiles\.
MsgBox NewestFile("c:\myfiles", "*.xls")
The worksheet formula below displays the same filename.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks