Search code examples
vbaloopsdatesubdirectory

VBA - Loop through multiple subfolders on a network location with date search criteria/ Improve search speed

Purpose of my question and of the VBA code: Get specific data (a couple columns) from each one of the "table.csv" file in a network directory. Each networkdirectory/subfolders01/subfolders02 contains one "table.csv" file but 100 other subfolders are included in each network/subfolders01. The other folders are not needed, the only one we are interested in is subfolder02 for each subfolder01. The number of subfolders01 in the network directory is about 15000. However I only need subfolders01 from Jan2020 to Apr2020,for example (200 subfolders). Final purpose is to trend data.

Issue: I am trying to understand how I could improve the VBA code that I am currently using. This code goes through each subfolder one by one and then check the date and file name. I am wondering if there is a way to add any search filters criteria for subfolder date and name to have a faster loop. How can we avoid the code to go through each subfolders?

Please see below the code I am using, I really appreciate your time and hope my request is clear.

'''

Function GetFiles(startPath As String) As Collection 
Dim fso As Object, rv As New Collection, colFolders As New Collection, fpath As String
Dim subFolder As Object, f, dMinfold, dtMod
Set fso = CreateObject("Scripting.FileSystemObject")

dMinfold = ThisWorkbook.Sheets("Enter_Date").Cells(2, 1)

colFolders.Add startPath

Do While colFolders.Count > 0
    fpath = colFolders(1)
    colFolders.Remove 1
    'process subfolders
    For Each subFolder In fso.getfolder(fpath).subfolders
        If subFolder.DateLastModified >= dMinfold Then
            colFolders.Add subFolder.Path
        End If
    Next subFolder
    'process files
    f = Dir(fso.buildpath(fpath, "*Table.csv"), vbNormal)
    Do While f <> ""
        f = fso.buildpath(fpath, f)
        dtMod = FileDateTime(f)
        If dtMod >= dMinfold And Right(f, 3) = "csv" Then
            rv.Add f
        End If

        f = Dir()
    Loop
Loop
Set GetFiles = rv
End Function'''

Then I have my code to get transfer data from each file. Thank you.

Solution

  • I'll put in screenshots to clear up the Get & Transform method, since it is the GUI approach rather than code.

    It is possible to filter before loading contents, which will speed things up significantly. I tried with a few thousand subfolders filtered down to 20, loads instantly.

    Here's the initial screen for get data from folder Initial results for get data from folder

    You can then filter on path. In your case it will be based on the date from the folder name. enter image description here

    Now that it's filtered you can expand the content using the header button. enter image description here

    Inside content, you'll have to expand again to convert from csv to excel table enter image description here

    Choose/rename columns as needed, then hit "close and load" to drop it into excel. Default is to a new table, but you can "load to" if something more custom is needed. enter image description here

    Here's your output. You can right-click refresh or refresh from vba as needed. enter image description here

    Edit- Just noticed that I used .txt rather than .csv for the files. Might change how a step or two looks in the middle, but the general idea is the same.