Go through subfolders, select matching files and copy a worksheet from each into a master file

[Rainmanne]

I've got a big folder with weekly reports in a format "YYYY-MM.DD FILE NAME.xlsx". Each report has a worksheet named "DD report". There are 2.5 years of weekly report. I need to analyse them and I want to copy the "DD report" worksheet from each into one master file under the date name (so "DD report" from the example will become YYYYMMDD) . But it looks like quite an endeavour for me. So far I have managed to come up with a code which apparently goes through all subfolders and finds the matching files (but it does not work if I put "*FILE NAME.xlsx" as a pattern but looks like working with a different file like "MTM*.*". Now I am stuck as I have no idea how to basically open each file, copy the sheet and rename it. I can probably manage to copy and rename operations but I do not know how to start with opening each matching file. Any help please?

Sub FindPatternMatchedFiles()

    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Dim objRegExp As Object
    Set objRegExp = CreateObject("VBScript.RegExp")
    objRegExp.Pattern = "MTM*.*"
    objRegExp.IgnoreCase = True

    Dim colFiles As Collection
    Set colFiles = New Collection
    
    Dim folderName As String
    Dim folder As Integer
    
    'Set the folder name to a variable
        folder = Application.FileDialog(msoFileDialogFolderPicker).Show
            If folder <> 0 Then
                folderName = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

            End If
    

    RecursiveFileSearch folderName, objRegExp, colFiles, objFSO

    For Each f In colFiles
        Debug.Print (f)
        'Insert code here to do something with the matched files
    Next

    'Garbage Collection
    Set objFSO = Nothing
    Set objRegExp = Nothing

End Sub

Sub RecursiveFileSearch(ByVal targetFolder As String, ByRef objRegExp As Object, _
                ByRef matchedFiles As Collection, ByRef objFSO As Object)

    Dim objFolder As Object
    Dim objFile As Object
    Dim objSubFolders As Object

    'Get the folder object associated with the target directory
    Set objFolder = objFSO.GetFolder(targetFolder)
    
    MsgBox targetFolder

    'Loop through the files current folder
    For Each objFile In objFolder.Files
        If objRegExp.test(objFile) Then
            matchedFiles.Add (objFile)
        End If
    Next

    'Loop through the each of the sub folders recursively
    Set objSubFolders = objFolder.SubFolders
    For Each objSubfolder In objSubFolders
        RecursiveFileSearch objSubfolder, objRegExp, matchedFiles, objFSO
    Next

    'Garbage Collection
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objSubFolders = Nothing

End Sub

 

[BatTodor]

Are you restricted to use VBA for this task?
It can be done easy with Power Query.

 

[Rainmanne]

Are you restricted to use VBA for this task?
It can be done easy with Power Query.

To be honest I have never used Power Query before. How I can use it in this case?

 

[Rainmanne]

Wow! It looks pretty cool. I wonder if I can use Power Query to aggregate all the reports in one file? Basically there are around 150 reports, which have the same format. I need to keep the first column and copy the data from 6th column from each report. I can see how I can filter the reports. But how I fix the first column and get the data?

 

[BatTodor]

To be honest I have never used Power Query before. How I can use it in this case?

There are at least two ways:
1. Standard way, using Data -> Get Data -> From File -> From Folder. This is easier way, can see what you do. But it's not efficient, would load data slowly (in most cases).

2. More complex, but more efficient way - can read it here Combine or Append Data: Optimal Combination Pattern — The Power User. This way will load data faster (in most cases).

 

[BatTodor]

Wow! It looks pretty cool. I wonder if I can use Power Query to aggregate all the reports in one file? Basically there are around 150 reports, which have the same format. I need to keep the first column and copy the data from 6th column from each report. I can see how I can filter the reports. But how I fix the first column and get the data?

If your data is similar, depends of how you prefer to do it - standard or more efficient way, it's not so hard to do it.
In any way, depends from your sources.
For example, an idea, which I use:
1. Unpivot all data. This will transform your data in two columns: first of column will be with column names, second with data.
2. Identify key columns. With keyword (for example, header of the column) you can filter it.
3. Load just key columns, or make another transformations.

If you upload sample data, I will try to help you.

 

[Domenic]

For your VBA code, trying replacing the following...

    For Each f In colFiles
        Debug.Print (f)
        'Insert code here to do something with the matched files
    Next

with

    '
    '
    '

    Dim destinationWorkbook As Workbook
    Dim destinationWorksheet As Worksheet
    Dim currentWorkbook As Workbook
    Dim currentRange As Range
    Dim NextRow As Long
   
    Set destinationWorkbook = Workbooks.Add(xlWBATWorksheet)
    Set destinationWorksheet = destinationWorkbook.Worksheets(1)
   
    NextRow = 2
    For Each f In colFiles
        Debug.Print (f)
        'Insert code here to do something with the matched files
        Set currentWorkbook = Workbooks.Open(Filename:=f, UpdateLinks:=False, ReadOnly:=True) 'set UpdateLinks to True if you're workbooks can contain links, and you want them updated
        Set currentRange = currentWorkbook.Worksheets(1).UsedRange
        currentRange.Copy destinationWorksheet.Cells(NextRow, "A")
        NextRow = NextRow + currentRange.Rows.Count
        currentWorkbook.Close SaveChanges:=False
    Next
   
    'etc
    '
    '

Also, if you only want the values copied to worksheet without the formatting, etc, try replacing...

        currentRange.Copy destinationWorksheet.Cells(NextRow, "A")

with

        With currentRange
            destinationWorksheet.Cells(NextRow, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With

Hope this helps!

 

[Rainmanne]

If your data is similar, depends of how you prefer to do it - standard or more efficient way, it's not so hard to do it.
In any way, depends from your sources.
For example, an idea, which I use:
1. Unpivot all data. This will transform your data in two columns: first of column will be with column names, second with data.
2. Identify key columns. With keyword (for example, header of the column) you can filter it.
3. Load just key columns, or make another transformations.

If you upload sample data, I will try to help you.

Thanks so much for your help! Not sure that I can upload the data. I can create a fake sample though.

In any way, this is what I've done so far:

1. Get Data - Selected the folder
2. Filtered the files I need
3. Combine files - Selected the worksheet I need

Now I have all the columns with the data from each file under each other. I can see that I can remove the columns I do not need. But what should I do next? How can I get the first column with the row names and then columns for each file data?

 

[Rainmanne]

Also from one point the columns shift (an extra column is added). I wonder if it's possible to realign them in the same query or I will have to do two queries for each dataset.

 

[Rainmanne]

Also, when I go to Get Data and select From Folder, what should I do next: Combine, Load or Transform Data? I have selected Transform Data but now I am not sure if it was correct.