Finding The Most Or Least Recent File In A Folder

This pages describes a VBA function that will return the name of the most recently or least recently modified file in a specified folder. You can specify one or more file extensions to include in the search. .

Introduction

This page describes a VBA function named GetRecentFile. This function will return the name of the most recently modified or the least recently modified file in a specified folder. You can narrow the files to be examined by specifying one or more file extensions or you can examine all files in the folder.

The procedure declaration is as follows:

    Function GetRecentFile(DirPath As String, Extension As Variant, _
        Optional LeastRecent As Boolean = False) As String

The input parameters to GetRecentFile are as follows:

DirPath
This is the folder name to search. This must be a fully qualified (drive and folder name) path and the folder must exist. If the folder does not exist, the result of the function is vbNullString.

Extension
This specifies the extension(s) of the files to test. It may be (1) the string * or an empty string (vbNullString) to search all files in the folder, (2) a single string specifying the file extension (e.g., xls to look at Excel 97/2003 workbooks), (3) or an array of one or more file extensions (e.g., Array ("xls","xlsm","xlsx") for Excel 97/2003 and 2007 workbooks).

LeastRecent
This indicates whether to return the name of the least recently (oldest) or the most recently (newest) modified file. If this parameter is False or is omitted, the name of the most recently modified file is returned. If this parameter is True, the name of the least recently modified file is returned.

The Code

You can download a module file containing the code below. The code for the GetRecentFile function is shown below.

Function GetRecentFile(DirPath As String, Extension As Variant, _
    Optional LeastRecent As Boolean = False) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetRecentFile
' By Chip Pearson, www.cpearson.com/Excel, chip@cpearson.com
'
' This procedure returns the most recent or least recent file name in a folder
' specified by DirPath having an extension specified by Extension.
' The parameters are as follows:
'
'       DirPath         The folder to search. This folder must be a fully
'                       qualified (drive and folder info) folder name and this
'                       folder must exist.
'
'       Extension       The file extension to match. This may be a simple string,
'                       (e.g., "xls" for Excel 97/2003 workbooks), an array of
'                       strings (e.g., Array("xls","xlsm","xlsx") for Excel
'                       97/2003 and 2007 workbooks). If Extension is either
'                       vbNullString or "*", all file extensions are included.
'
'       LeastRecent     If omitted or FALSE, the most recently modified file
'                       is returned. If TRUE, the least recently modified file
'                       is returned.
'
' The result is the fully qualifed file name of the most or least recent file
' name or vbNullString if no matching files were found in DirPath. vbNullString
' is returned if DirPath does not exist or is not accessible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim SaveDir As String           ' saved CurDir setting
    Dim FileName As String          ' FileName returned by Dir()
    Dim CompareDateTime As Double   ' Last date time of file
    Dim SaveFileName As String      ' Saved FileName
    Dim CurrFileDate As Double      ' Current file datetime returned by Dir()
    Dim Ext As String               ' Temporary file extension to test
    Dim CurrFileExt As String       ' Current file's extension 
    Dim N As Long                   ' Array index variable 
    Dim Pos As Long                 ' Position indicate of extension 
    Dim CompResult As Boolean       ' File test flag 
        
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Save the current working directory setting
    ' and then change the working directory to
    ' DirPath. Exit with result vbNullString if
    ' DirPath does not exist.
    ''''''''''''''''''''''''''''''''''''''''''''' 
    SaveDir = CurDir
    On Error Resume Next
    ChDrive DirPath
    If Err.Number <> 0 Then
        ' Debug.Print "Invalid Path: " & DirPath 
        Exit Function
    End If
    ChDir DirPath
    If Err.Number <> 0 Then
        ' Debug.Print "Invalid Path: " & DirPath 
        Exit Function
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Determine which file types to look at.
    ' If Extension is an array, look at all files.
    ' If Extension is vbNullString or "*", look
    ' at all files. If Extension is not an array
    ' and not "*" and not vbNullString, look
    ' only at the files with the specified
    ' extension. Call Dir() to prime the loop.
    ''''''''''''''''''''''''''''''''''''''''''''''' 
    If IsArray(Extension) = True Then
        FileName = Dir(DirPath & "\*.*")
    Else
        If (StrComp(Extension, vbNullString, vbBinaryCompare) = 0) Or _
            (StrComp(Extension, "*", vbBinaryCompare) = 0) Then
            FileName = Dir(DirPath & "\*.*")
        Else
            FileName = Dir(DirPath & "\*." & Extension)
        End If
    End If
                        
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' If we are looking for the oldest file, we
    ' need to initialize CompareDateTime to a
    ' date past (greater than) any existing file
    ' date. Just use year = 9999.
    '''''''''''''''''''''''''''''''''''''''''''''' 
    If LeastRecent = True Then
        CompareDateTime = DateSerial(9999, 1, 1)
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Look at all file names returned by Dir,
    ' looping until Dir returns a vbNullString.
    '''''''''''''''''''''''''''''''''''''''''''''' 
    Do Until FileName = vbNullString
        FileName = DirPath & "\" & FileName
        CurrFileDate = FileDateTime(FileName)
        CompResult = False
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Test the current file's modification date
        ' against the stored CompareDateTime. If
        ' the datetime is less than the saved time
        ' (or greater than, in case LeastRecent is
        ' False), set the test flag to True.
        ' Otherwise, don't test the file.
        ''''''''''''''''''''''''''''''''''''''''''''' 
        If LeastRecent = True Then
            If CurrFileDate < CompareDateTime Then
                CompResult = True
            Else
                CompResult = False
            End If
        Else
            If CurrFileDate > CompareDateTime Then
                CompResult = True
            Else
                CompResult = False
            End If
        End If        
        
        If CompResult = True Then
            ''''''''''''''''''''''''''''''''''''''''''''''''''
            ' Get the extension of the current file
            ' and test it against either all the
            ' extensions in the Extension array or
            ' against the specified (single) extension
            ' or, if Extension is either "*" or vbNullString,
            ' against any extension.
            '''''''''''''''''''''''''''''''''''''''''''''''''' 
            Pos = InStrRev(FileName, ".")
            If Pos > 0 Then
                CurrFileExt = Mid(FileName, Pos + 1)            
                If IsArray(Extension) = True Then
                    ''''''''''''''''''''''''''''''''''''''
                    ' Extension is an array. Loop through
                    ' the extensions in the array. If two
                    ' filename differ only by extension and
                    ' have the same date/times, the last
                    ' one returned by Dir() will be the
                    ' result (very unlikely to occur).
                    '''''''''''''''''''''''''''''''''''''' 
                    For N = LBound(Extension) To UBound(Extension)
                        Ext = Extension(N)
                        If StrComp(Ext, CurrFileExt, vbTextCompare) = 0 Then
                            CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                        End If
                    Next N
                Else
                    ''''''''''''''''''''''''''''''''''''''''
                    ' If extension is a "*" or vbNullString,
                    ' then the current file becomes the
                    ' saved file (no testing of file extension
                    ' is done).
                    '''''''''''''''''''''''''''''''''''''''' 
                    If (StrComp(Extension, "*", vbBinaryCompare) = 0) Or _
                        (StrComp(Extension, vbNullString, vbBinaryCompare) = 0) Then
                             CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                    Else
                        '''''''''''''''''''''''''''''''''''''
                        ' Extension was specified. Ensure the
                        ' current FileName has the specified
                        ' extension.
                        ''''''''''''''''''''''''''''''''''''' 
                        If StrComp(CurrFileExt, Extension, vbTextCompare) = 0 Then
                             CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                        End If
                    End If
                End If
        
            End If
        End If
        
        '''''''''''''''''''''''''''''''
        ' Get the next file name from
        ' the Dir function.
        ''''''''''''''''''''''''''''''' 
        FileName = Dir()
    Loop
    
    '''''''''''''''''''''''''''''''''''
    ' Restore the current working
    ' directory and return SaveFileName
    ' as the result. If no matching file
    ' was found, SaveFileName will be
    ' vbNullString.
    ''''''''''''''''''''''''''''''''''' 
    ChDrive SaveDir
    ChDir SaveDir
    GetRecentFile = SaveFileName
End Function

You can call the GetRecentFile function with code such as the following:

Sub AAA()
    Dim FileName As String
    Dim ModDate As Date
    Dim FilePath As String
    Dim Ext As Variant
    FilePath = "C:\Temp" ' <<< Change to appropriate folder name 
    'Ext = "" ' <<< SIMPLE STRING EXTENSION 
    ' OR 
    Ext = Array("xls", "xlsm", "xlsx") '<< ARRAY OF EXTENSIONS 
    FileName = GetRecentFile(DirPath:=FilePath, Extension:=Ext, LeastRecent:=True)
    If FileName = vbNullString Then
        Debug.Print "No file found"
    Else
        ModDate = FileDateTime(FileName)
        Debug.Print "File: " & FileName, "Modified: " & ModDate
    End If
End Sub

You can download a module file containing the code above.

This page last updated: 24-December-2007

Created by Chip Pearson at Pearson Software Consulting, LLC
Email: chip@cpearson.com Before emailing me, please read this page. 
http://cpearson.com/excel/GetFileName.aspx
Copyright © 1997 - 2007, Charles H. Pearson



About This Web Site Activate The Main Excel Window Adding Dates Adding The COM Add-Ins Menu Item Adding the COM Add-Ins menu item Alerter Display Library Alerter Message Display Component Analysis ToolPak Application Events Array Formulas Array Formulas Arrays In VBA Arrays To Columns Attachments In Newsgroups Automation Add-Ins Blinking Text Blinking Text Bracket Pricing Browse Folder CALL Function Call Stack Calling Worksheet Functions Cell Referencing Cell View Add-In Class Modules Clipboard Functions Clone A Folder Code Modules Code Notes Collections And Dictionaries Color Band Formatting Colors And Functions COM Add-Ins In VB6 Conditional Formatting Conditional Formatting Colors Connected And Disconnected Objects Consulting And Development Services Creating An Add-In Date And Time Quick Entry Date And Time VBA Functions Date And Time Worksheet Functions Date And Times In Excel Date Arithmetic Date Intervals DATEDIF Function Dates And Times Daylight Savings Time Debugging VBA Declaring Variables Default Property Of A Class Deleting Duplicates Deleting Duplicates With Filter Detecting Scrolling Direct Connect The MSNEWS Directory Tree Listings Distributed Dates Document Properties Downloadable Files Downloadable Zip Files Downloads Duplicate Data In Lists And Ranges Duplicates In Lists Email Me Emailing ME Empty A Folder Enum Data Types Error Handling In VBA Error Values And The FormatMessage API EULA Answers Event Procedures Events In UserForms Excel Macros Excel Pages Excel Shutdown Excel Tools Excel Worksheet Functions Feet And Inches File Name Of COM Add-In File Times Find In Range First And Last Names Folder Tree Listing Folder Tree Listing Form Positioner Formula Bar Shortcut Fractional Arithmetic Functions And Subs Games Get And Set Properties GetInfo Function Headers And Footers Hidden Namespace Holidays Import And Export Text Importing Large Files To Excel Importing Large Text Files INDIRECT Function Insert And Fill A Series Insert Procedure Names In VBA Intro To VBA Julian Dates Keyboard Shortcuts Keyboard Tests Last Time Of File Latitude And Longitude Legalese Legalese and Copyright Information Links List Lookups ListBox Utlities Lists Of Data In Excel Lists On Worksheets Local And GMT Times Main Page Main Page Make Mulitple Levels Of Directories Menu Items Menus In The VBA Editor Modifying The Registry Multi File Import In VBA Multiple Monitors And Windows Name Box Shortcut Named Ranges Nested Functions New Posters Guide For Newsgroups No Blanks No Dupicate Entries Office Application Window Classes Optimize Your VBA Code Ordinal Number Suffixes Overtime And Timesheets Parsing Phone Numbers Passing And Returning Arrays In VBA Passwords Pivot Tables Popup Box Privacy Statement Progress Bar Random Numbers Ranking Data Recursive Programming Recycle A File Or Folder References Registry Related Pages Relative And Absolute References Relative And Absolute References Resources For Developers Returning Arrays Rounding In Excel Rowliner Add-In Save Copy And Zip A Workbook Save Coy And Zip Add-In Scheduling Procedures With OnTime Search The Site Search The Site Series Information SetParent And Userforms Sheet Referencing Shell And Wait Shortcut Keys Shortcut Keys For VBA Show Any Form Site Feedback Size String Some Friendly Sort And Array In VBA Sort By Color Sort Worksheet Tabs Sorting Arrays Of Objects Sorting Objects Comparison Special Characters Special User Folders Startup Errors In Excel Status Bar In Excel Subclassing Windows Support Suppress Changes In Forms System Error Messages From Numbers Temporary Files And Folders Test Friendly Text Box Validation Time Zones Timed Close Topic Index Truly Global Variables Unselect A Cell Or Area Upper And Lower Case User Defined Functions User Defined Functions Validate A Text Box Values And Formulas Variables In VBA VBA Code To Modify VBA Code Versions Of Excel Wait For File Close Week Numbers What's New On The Site Widen The Name Box Word Count Of Worksheet Zomming And Centering  
0 - Rate This Page 1 - Not Helpful 2 - Somewhat Helpful 3 - Quite Helpful 4 - Very Helpful 5 - Exactly What I Needed


Essential Tools For Developers


Ready

Advertise Your Product On This Site