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


Search for Tips


All Tips

Browse Tips by Category

Tip Books

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

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy