Opening all Workbooks in Folder/Subfolder/Subfolder and copying information

[Martin Dyulgyarov]

I am trying to create a masterfile, which contains partial information from workbooks, which are contained in a folder (which has two main subfolders and the workbooks are in these subfolders' subfolders). I have tried workarounds so that I do not have to use a subfolder's subfolder's, but it makes the whole process of automation obsolete as it takes more time. I have tried two different codes, which I found on this website, but both do not seeem to work.

First code:

Public Sub Add_Master_Sheet_To_All_Workbooks_All_Subfolders_LB()
   
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Process_Workbooks_In_Folder "F:\Work\Test\Order\"
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
   
    MsgBox "Done"
   
End Sub


Private Sub Process_Workbooks_In_Folder(folderPath As String)
  
    Static FSO As Object
    Dim Folder As Object, Subfolder As Object, File As Object
   
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    'Process files in this folder
   
    Set Folder = FSO.GetFolder(folderPath)
   
    For Each File In Folder.Files
        If File.Name Like "*.xls" Then
            Add_Master_Sheet File.Path
        End If
    Next
   
    'Process files in subfolders
   
    For Each Subfolder In Folder.SubFolders
        Process_Workbooks_In_Folder Subfolder.Path
    Next

End Sub


Private Sub Add_Master_Sheet(workbookFilepath As String)

    Dim wb As Workbook
    Dim masterSheet As Worksheet
    Dim i As Integer
   
    Set wb = Workbooks.Open(workbookFilepath)
   
    With wb
       
        'Does the Master sheet already exist in this workbook?
       
        Set masterSheet = Nothing
        On Error Resume Next
        Set masterSheet = .Worksheets("Master")
        On Error GoTo 0
       
        If masterSheet Is Nothing Then
       
            'No, so add the Master sheet at the end
           
            Set masterSheet = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
            masterSheet.Name = "Master"
        End If
       
        'List all worksheet names
       
        For i = 1 To .Worksheets.Count
            masterSheet.Cells(i, 1).Value = .Worksheets(i).Name
        Next

        .Close saveChanges:=True
       
    End With

End Sub

The second code was:

Sub OpenAllWorkbooks()
'Step 1:Declare your variables
    Dim MyFiles As String
'Step 2: Specify a target folder/directory, you may change it.
    MyFiles = Dir("d:\Temp\*.xlsx")
    Do While MyFiles <> ""
'Step 3: Open Workbooks one by one
    Workbooks.Open "d:\Temp\" & MyFiles

    'run some code here
    MsgBox ActiveWorkbook.Name

    ActiveWorkbook.Close SaveChanges:=True

'Step 4: Next File in the folder/Directory
    MyFiles = Dir
    Loop
End Sub

Any help would be appreciated. Thank you!

 

[Larry Haydn]

Suggestion:
List your workbooks' full path and filename in a worksheet.
Run a LOOP to open each workbook in this list, do whatever you need, then close it.