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 some users have reported problems using the FileSearch object.
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
Usage Examples
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.
=NewestFile("c:\myfiles", "*.xls")