VBA - Find oldest NAMED folder in a directory

[RockandGrohl]

UGH, I'VE WRITTEN OLDEST, I MEANT NEWEST.
Hello, I'm writing some code to look into a folder and find the oldest named folder. Our folders are structured like this:

2023-01
2022-12
2022-11
2022-10

etc

But because folders can have different creation & modification dates, these can't be relied on to produce a chronological order.

Is there a simple way to determine the folder with the newest date, by name, which I can then insert into this workbook.open command?

Workbooks.Open "C:\Users\" & uname & "\[redacted company name]\Reporting - Documents\[redacted customer name]\Reporting\Mobile\[redacted supplier name]\>>FOLDER MONTH NAME GOES HERE\TargetWorkbook.xlsx"

Thank you!

 

[Domenic]

Try the following code . . .

Option Explicit

Sub test()

    Dim latestFolderName As String
    latestFolderName = getLatestFolderName("c:\users\domta\desktop") 'change the path to your directory accordingly
    
    If Len(latestFolderName) = 0 Then
        MsgBox "No folders found!", vbExclamation
        Exit Sub
    End If
    
    MsgBox "The latest folder is '" & latestFolderName & "'", vbInformation
    
    'etc
    '
    '
    
End Sub

Function getLatestFolderName(ByVal mainFolderName As String) As String

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim mainFolder As Object
    Set mainFolder = fso.getfolder(mainFolderName)
    
    Dim latestDate As Date
    Dim latestFolderName As String
    Dim currentDate As Date
    Dim currentFolder As Object
    
    latestDate = 0
    latestFolderName = ""
    For Each currentFolder In mainFolder.subfolders
        If currentFolder.Name Like "####-##" Then
            currentDate = currentFolder.Name
            If currentDate > latestDate Then
                latestDate = currentDate
                latestFolderName = currentFolder.Name
            End If
        End If
    Next currentFolder
    
    getLatestFolderName = latestFolderName
    
End Function

Hope this helps!

 

[RockandGrohl]

Try the following code . . .

Option Explicit

Sub test()

    Dim latestFolderName As String
    latestFolderName = getLatestFolderName("c:\users\domta\desktop") 'change the path to your directory accordingly
   
    If Len(latestFolderName) = 0 Then
        MsgBox "No folders found!", vbExclamation
        Exit Sub
    End If
   
    MsgBox "The latest folder is '" & latestFolderName & "'", vbInformation
   
    'etc
    '
    '
   
End Sub

Function getLatestFolderName(ByVal mainFolderName As String) As String

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    Dim mainFolder As Object
    Set mainFolder = fso.getfolder(mainFolderName)
   
    Dim latestDate As Date
    Dim latestFolderName As String
    Dim currentDate As Date
    Dim currentFolder As Object
   
    latestDate = 0
    latestFolderName = ""
    For Each currentFolder In mainFolder.subfolders
        If currentFolder.Name Like "####-##" Then
            currentDate = currentFolder.Name
            If currentDate > latestDate Then
                latestDate = currentDate
                latestFolderName = currentFolder.Name
            End If
        End If
    Next currentFolder
   
    getLatestFolderName = latestFolderName
   
End Function

Hope this helps!

Hah! I Love that! So it just runs through all folders and if they fit specifically the critiera of being ####-## then it finds the oldest one. Fantastic.


Here's what I came up with...

curDate = Format(Now, "YYYY-MM")
prevDate = Format(DateAdd("M", -1, Now), "YYYY-MM")

folder = Dir("C:\Users\" & uname & "\Company\Reporting - Documents\Customer\Reporting\Mobile\Supplier\" & curDate, vbDirectory)

If folder = "" Then
    ' Latest month doens't exist, opening previous month
Workbooks.Open "C:\Users\" & uname & "\Company\Reporting - Documents\Customer\Reporting\Mobile\Supplier\" & prevDate & "\" & prevDate & " Supplier UsageReport Customer.xlsx"
Set rep = ThisWorkbook
Else
    ' Latest month does exist
Workbooks.Open "C:\Users\" & uname & "\Company\Reporting - Documents\Customer\Reporting\Mobile\Supplier\" & curDate & "\" & curDate & " Supplier UsageReport Customer.xlsx"
Set rep = ThisWorkbook

End If

But as you can see, it only has the capacity to check for two folders, and only if they are within ~2 months of the current YYYY-MM format.

 

[Domenic]

That's great, I'm glad to hear that it helps, cheers!