Identifying The Newest File In A Directory

Category: VBA Functions | [Item URL]

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

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")