4rth most recent file from the file name date

[torvaia]

Hi ,
I have a folder with different files, updated every week. One file is added per week.
I need a VBA code which take the name of the most recent file and the 4rth one based on the date founded in the file name in the format "YYYY-Month-DD Targets - ALL.xls", eg. "2023-Apr-24 BDD Targets - ALL.xls".

Could someone help? This below part of my code..

For Each objfile In myFolder.Files
                    
            If InStr(1, objfile.Name, ".xls") > 0 Then
                    i = i + 1
                    fileName = objfile.Name
                    dateParts = Split(fileName, "-")
                    yearPart = dateParts(0)
                    monthPart = Month(DateValue(dateParts(1) & " 1, 2021")) ' Convert month name to number
                    dayPart = Left(dateParts(2), 2)
                    dateStr = DateSerial(yearPart, monthPart, dayPart)

Thanks a lot.

 

[John_w]

Try this:

Public Sub Most_Recent_Files_In_Folder()

    Dim folderPath As String, fileName As String
    Dim filesArray As Object
    Dim fileNameDate As Date
    
    folderPath = "C:\path\to\files\"   'CHANGE THIS
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Set filesArray = CreateObject("System.Collections.ArrayList")
    
    fileName = Dir(folderPath & "*.xls")
    Do While fileName <> vbNullString
        fileNameDate = CDate(Left(fileName, 11))
        filesArray.Add CLng(fileNameDate) & "|" & fileName
        fileName = Dir()
    Loop
    
    filesArray.Sort
    filesArray.Reverse
    
    Debug.Print "Most recent: " & Split(filesArray(0), "|")(1)
    Debug.Print "4th most recent: " & Split(filesArray(3), "|")(1)
    
End Sub

 

[torvaia]

many thanks!!