VBA: Get Data from multiple folder and sub folders

[Xlacs]

Hello Good People,


To make it short so below code extract data from a multiple workbooks in a folder.
My question is, other files are located in the same drive but in a sub folder.

How to extract it ith the below code? =(

For example.

Z>My Items>Reports>June Folder>Team A Folder> (workbooks 1-10)
Z>My Items>Reports>June Folder>Team B Folder (workbooks 11-20)
Z>My Items>Reports>June Folder>Team C Folder (workbooks 21-30)

All workbooks are located in Drive Z>My items>Reports folder.

Any help would be greatly appreciated. Thanks in Advance.

Public Sub Copy_AutoFiltered_Rows_From_Workbooks()

    Dim matchFiles As String, folder As String, fileName As String
    Dim destCell As Range
    Dim fromWorkbook As Workbook
    Dim startDate As Date, endDate As Date
   
    'Folder and wildcard file spec of workbooks to import
   
    matchFiles = "C:\Users\Tim\Desktop\My Files\*.xlsm"
    'matchFiles = "D:\Temp\Excel\Workbooks\Draft*.xlsm"
    folder = Left(matchFiles, InStrRev(matchFiles, "\"))
   
    With ThisWorkbook.ActiveSheet
        If Not IsDate(.Range("A1").Value) Or IsEmpty(.Range("A1").Value) Or Not IsDate(.Range("A2").Value) Or IsEmpty(.Range("A2").Value) Then
            MsgBox "Cells A1 and A2 must contain a date"
            Exit Sub
        End If
        startDate = .Range("A1").Value
        endDate = .Range("A2").Value
        If startDate > endDate Then
            MsgBox "Start date in A1 must be earlier than end date in A2"
            Exit Sub
        End If
        Set destCell = .Cells(.Rows.Count, "B").End(xlUp)
    End With
   
    Application.ScreenUpdating = False
   
    fileName = Dir(matchFiles)
    While fileName <> vbNullString
        Set fromWorkbook = Workbooks.Open(folder & fileName, ReadOnly:=True)
        With fromWorkbook.Worksheets(1)
            'Filter column B between start date and end date
           
            .Range("B8").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(startDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)
           
            If destCell.Row = 1 Then
                'Copy header row and data rows
                .Range("B8").CurrentRegion.Copy destCell
            Else
                'Copy only data rows
                .Range("B8").CurrentRegion.Offset(1).Copy destCell
            End If
        End With
        fromWorkbook.Close False
       
        With destCell.Worksheet
            Set destCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1)
        End With
       
        DoEvents
        fileName = Dir
    Wend
   
    Application.ScreenUpdating = True
   
    MsgBox "Finished"
   
End Sub

 

[Micron]

You need what's called a recursive function - one that looks at the files in a folder and does what you want with them, but also if one of the folder items is a folder, opens that folder and does the same. The 2nd level folder may contain subfolders, the 3rd level may contain subfolders and so on. AFAIK, file processing can start from the bottom up or process everything in a folder, then open each subfolder in turn and do the same. I have no code for this (did but lost it when a hd crashed) so I can't help beyond telling you what you need to look for.

 

[JEC]

Will not write the complete code for you but this is what @Micron means. The recursive code searches through folders and all subfolders.
Change your path and it will search for all xlsm files and lists them in column L. You can easily adapt this code as you desire.

Dim ar(), x As Long

Public Sub file_search()
 getFile "C:\Users\xxx\Downloads\"
 Range("L2").Resize(x) = Application.Transpose(ar)
End Sub

Public Sub getFile(objFolderPath As String)
 Dim sFold, it, sf As Variant
 With CreateObject("scripting.filesystemobject")
    Set sFold = .GetFolder(objFolderPath)
    For Each it In sFold.Files
       If .GetExtensionName(it) = "xlsm" Then
          ReDim Preserve ar(x)
          ar(x) = it.Path
          x = x + 1
       End If
    Next
    For Each sf In sFold.SubFolders
       getFile sf.Path
    Next
 End With
End Sub

 

[Xlacs]

Will not write the complete code for you but this is what @Micron means. The recursive code searches through folders and all subfolders.
Change your path and it will search for all xlsm files and lists them in column L. You can easily adapt this code as you desire.

Dim ar(), x As Long

Public Sub file_search()
 getFile "C:\Users\xxx\Downloads\"
 Range("L2").Resize(x) = Application.Transpose(ar)
End Sub

Public Sub getFile(objFolderPath As String)
 Dim sFold, it, sf As Variant
 With CreateObject("scripting.filesystemobject")
    Set sFold = .GetFolder(objFolderPath)
    For Each it In sFold.Files
       If .GetExtensionName(it) = "xlsm" Then
          ReDim Preserve ar(x)
          ar(x) = it.Path
          x = x + 1
       End If
    Next
    For Each sf In sFold.SubFolders
       getFile sf.Path
    Next
 End With
End Sub

Argh. THank you.. ill try this one..

 

[Xlacs]

Im getting error ith this one.. Dunno hot to incorporate above codes in my current..

Im having an argument failed with this line getFile "C:\Users\xxx\Downloads\"

 

[JEC]

Yes you need to change that path to yours

 

[Micron]

Because you don't have a folder named xxx? You need to adapt as advised.
FWIW, this line is probably meant to declare all variables as Variant as per the last word.
Dim sFold, it, sf As Variant

The fact that sFold, it, sf are not explicitly declared means they are variants by default, not because the last one is declared as a variant. In this case it matters not, but in this case

Dim sFold, strInput, strSomething As String

it does matter. The first 2 are variants, the last is a string type. Doing this unintentionally means that if the application correctly interprets your intent you're OK. If not, you might be in for a bit of frustration trying to figure out what's causing errors or the wrong results.

 

[JEC]

Yes I did that intentionally. I used the default because they are all variants
I actually could have left "As Variant" out too in this line: Dim sFold, it, sf As Variant

 

[Micron]

I see it so much, and most people think that whatever the last variable is typed as applies to all of them on the line.

 

[JEC]

Yes a very common mistake. It's easy to assume though!