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]

The following code loops through each .xls file in the specified folder, and checks to make sure that the filename has an underscore before considering it for the latest file. Then it opens the latest file.

Option Explicit

Sub test()

    Dim wkbOpen As Workbook
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim MyDate As Long
    Dim LatestDate As Long
    Dim Cnt As Long
    
    [COLOR="SeaGreen"]'Change the path accordingly[/COLOR]
    MyPath = "C:\Path\"
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xls")
    
    Do While Len(MyFile) > 0
    
        If InStr(1, MyFile, "_") > 0 Then
        
            MyDate = CLng(Mid(MyFile, InStrRev(MyFile, "_") + 1, 8))
            
            If MyDate > LatestDate Then
            
                Cnt = Cnt + 1
            
                LatestDate = MyDate
                
                LatestFile = MyFile
                
            End If
            
        End If
        
        MyFile = Dir
        
    Loop
    
    If Cnt = 0 Then
        
       MsgBox "No file found...", vbInformation
       
       Exit Sub
       
    End If
       
    Set wkbOpen = Workbooks.Open(MyPath & LatestFile)
        
End Sub

 

[Domenic]

This is basically the same macro. The only difference is that instead of calling all .xls files and then checking for an underscore before finding the latest file, it calls only files with an underscore before finding the latest file.

Option Explicit

Sub test()

    Dim wkbOpen As Workbook
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim MyDate As Long
    Dim LatestDate As Long
    Dim Cnt As Long
    
    MyPath = "C:\Path\"
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*_*_*.xls")
    
    Do While Len(MyFile) > 0
    
        Cnt = Cnt + 1
            
        MyDate = CLng(Mid(MyFile, InStrRev(MyFile, "_") + 1, 8))
        
        If MyDate > LatestDate Then
                
            LatestDate = MyDate
            
            LatestFile = MyFile
            
        End If
        
    MyFile = Dir
        
    Loop
    
    If Cnt = 0 Then
        
       MsgBox "No file found...", vbInformation
       
       Exit Sub
       
    End If
       
    Set wkbOpen = Workbooks.Open(MyPath & LatestFile)
        
End Sub

 

[KGee]

Hi Domenic,

Thank you so much. Both pieces of code worked without a hitch and I ended up using the second one you listed. Since I'm using individual macros for each of the reports I figured I could replace the first two wildcards in the "MyFile" variable with the name of the actual report. Since we're just starting the process there aren't too many files in the folder being searched but that will change in time and I assume that will help speed up processing down the road.

Just for my understanding, I know what the loop is doing but as I step through the code I have questions on how it actually works, specifically the DIR commands. When I look at the value of the MyFile variable when it is initially set, it contains a single filename even though there are four files in the folder right now. It seems to pick the first file listed based on a filename sort. Then the "MyFile = Dir" statement seems to set it to the next file in the list. After the fourth pass, the value is empty so the length is 0 and that exits the loop. Is that the gist of how it works?

Thanks again.

 

[Domenic]

Hi Domenic,

Hi KGee,

Thank you so much. Both pieces of code worked without a hitch and I ended up using the second one you listed. Since I'm using individual macros for each of the reports I figured I could replace the first two wildcards in the "MyFile" variable with the name of the actual report. Since we're just starting the process there aren't too many files in the folder being searched but that will change in time and I assume that will help speed up processing down the road.

Very good...

Just for my understanding, I know what the loop is doing but as I step through the code I have questions on how it actually works, specifically the DIR commands. When I look at the value of the MyFile variable when it is initially set, it contains a single filename even though there are four files in the folder right now. It seems to pick the first file listed based on a filename sort. Then the "MyFile = Dir" statement seems to set it to the next file in the list. After the fourth pass, the value is empty so the length is 0 and that exits the loop. Is that the gist of how it works?

That's exactly it...

Thanks again.

You're very welcome!

 

[KGee]

Great, I'm sure I will be able to recycle this code for future applications. Do you happen to know if the code would work for "monthly" files? We have other reports which are named in MMMYYYY fashion and I'm wondering if the code could be modified to work under those circumstances and give me the most current file.

 

[Domenic]

For 'MMMYYYY', try...

Option Explicit

Sub test()

    Dim wkbOpen As Workbook
    Dim strPath As String
    Dim strFile As String
    Dim strDate As String
    Dim strLatestFile As String
    Dim dtDate As Date
    Dim dtLatestDate As Date
    Dim Cnt As Long
    
    [COLOR="SeaGreen"]'Change the path accordingly[/COLOR]
    strPath = "C:\Path\"
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    [COLOR="SeaGreen"]'Change the filename pattern for which to search[/COLOR]
    strFile = Dir(strPath & "*_*_*.xls")
    
    Do While Len(strFile) > 0
    
        Cnt = Cnt + 1
            
        strDate = Mid(strFile, InStrRev(strFile, "_") + 1, 7)
        
        dtDate = CDate(Left(strDate, 3) & ", 1," & Mid(strDate, 4))
        
        If dtDate > dtLatestDate Then
                
            dtLatestDate = dtDate
            
            strLatestFile = strFile
            
        End If
        
    strFile = Dir
        
    Loop
    
    If Cnt = 0 Then
        
       MsgBox "No file found...", vbInformation
       
       Exit Sub
       
    End If
       
    Set wkbOpen = Workbooks.Open(strPath & strLatestFile)
        
End Sub

 

[Littlemalky]

What if I want to do the same thing, but I don't have underscores in my file name. I just want to be able to open the file with the most recent date in the name.

 

[Domenic]

What if I want to do the same thing, but I don't have underscores in my file name. I just want to be able to open the file with the most recent date in the name.

Try...

Option Explicit

Sub OpenMostRecentFile()

    Dim strPath As String
    Dim strFile As String
    Dim strDate As String
    Dim TimePeriod As Integer
    Dim dtStartDate As Date
    Dim dtEndDate As Date
    Dim i As Long
    
    [COLOR="SeaGreen"]'Change the path, accordingly[/COLOR]
    strPath = "C:\Users\Domenic\Desktop\"
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    [COLOR="SeaGreen"]'Change the time period, accordingly (30 = last 30 days; 60 = last 60 days; etc.)[/COLOR]
    TimePeriod = 30

    dtStartDate = Date
    dtEndDate = Date - TimePeriod
    
    For i = dtStartDate To dtEndDate Step -1
    
        [COLOR="SeaGreen"]'Change the date format, accordingly[/COLOR]
        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
    
    MsgBox "No files were found within the specified time period...", vbInformation
    
End Sub

 

[Littlemalky]

That looks like it will work, but when I try it it says No File Can Be Found blah blah. I changed the director so it links to the accurate folder and I changed the date formatting to m.dd.yy. Other than that what do i need to change? The files are all called "New Release Tracker 1.21.11.xlsx" with obviously different dates. I update this file twice a week and have to change the date everytime so it should work within the 30 day confines...