Get most recent date from list of filenames in folder

[KGee]

I have a report where I need to extract data from a previous report file and add it to the current report file being created. The report files are generated at random intervals so I want to write a macro to identify the most recent version of a report file in a specific folder. I need to base it off the file name and not the modified date because it's possible that older reports may still require edits after newer ones.

The report files all have a standard naming convention. It starts with the report name which is always two words split by an underscore but the name and length will vary based on the report. The report name is followed by another underscore then the date of the report file. Immediately following that is the department code, which is four characters, enclosed in parenthesis. See below for example.
"Report_Name_YYYYMMDD(DEPT).xls"

The departments will have separate files for generating each type of report so the only thing I'm concerned with is determining the date of the last report created. I am pulling the directory path to search from a cell in the spreadsheet. I can extract the date from the filename but need help setting it up to loop through each file in the folder and determine which is the most current file. It would be nice to have it return the full filename because I will have to open the file in the next step but I can figure that out if necessary.

 

[Domenic]

Try replacing...

    For i = dtStartDate To dtEndDate Step -1
    
        'Change the date format, accordingly
        strDate = Format(i, "yyyymmdd")
        
        If Len(Dir(strPath & "*" & strDate & "*.xls")) > 0 Then
            strFile = Dir(strPath & "*" & strDate & "*.xls")
            Workbooks.Open strPath & strFile
            Exit Sub
        End If
        
    Next i

with

    For i = dtStartDate To dtEndDate Step -1
    
        strDate = Format(i, "m.dd.yy")
        
        If Len(Dir(strPath & "*" & strDate & ".xlsx")) > 0 Then
            strFile = Dir(strPath & "*" & strDate & ".xlsx")
            Workbooks.Open strPath & strFile
            Exit Sub
        End If
        
    Next i

Note, however, if you have a file with a date such as "2.1.11", instead of "2.01.11", you'll need to change the format as follows...

strDate = Format(i, "m.d.yy")