Loop-ing through folders based on DATE (taking remaining dates) VBA

Question

I wrote a code in VBA that takes data from a lot of individual files from separate daily folders (e.g. 2021-10-09 is a folder).

The way it works now is like this: I select through some inputboxes the day and month and then my code runs on that specific folder in the filepath.

What I want is: Based on today's date (either from formula in vba or input), to create a loop that runs my code for the remaining days. For example, if today is 10/11/2021 and the last time I ran the code was 10/07, the code should loop through 10/08, 10/08, 10/10 and 10/11 lastly.

I can create a variable that takes the date string from last row to see when it was last updated. But how do i loop through those folders (with dates on them - attached SS).

Folders Structure

Thank you, Dan

Could you share the code you wrote? Please have a read of [ASK] and [MCVE]. — Darren Bartrup-Cook, Oct 11 '21 at 14:40

score 0 · Accepted Answer · answered Oct 11 '21 at 14:35

Use a FileSystemObject to loop through the Folders collection and use string comparison to filter for the ones you want to process.

Option Explicit

Sub ScanFolders()
    
    Const FOLDER_PATH = "C:\temp\so\test\"

    Dim FSO As Object, fld
    Dim dtLastRun As Date
    dtLastRun = Range("A1")

    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    For Each fld In FSO.getfolder(FOLDER_PATH).SubFolders
        If (fld.Name > Format(dtLastRun, "yyyy_mm_dd")) And _
           (fld.Name <= Format(Now, "yyyy_mm_dd")) Then
            ' Debug.Print fld.Name
            ' process files
        End If
    Next
    ' update sheet
    Range("A1") = Now

End Sub

Loop-ing through folders based on DATE (taking remaining dates) VBA

1 Answers1