Recursive Loop won't end (keeps re-pulling files)

[ZLeonhart]

Hey Everyone!

I've been trying to get an excel VBA macro to work by it keeps messing up.
The goal is for the macro to open every specific file in all the sub-folders of the main folders.

Example: If Main folder has 3 sub-folders called 1, 2, and 3, it'll go into each of those folders and pull the file, merge it into the Workbook running the macro.

However, at the moment, after it pulls the files, it reloops through the subfolders all over again.
I'm really new to this and can't figure out what's going wrong. Please help!

Script;

<sub>Sub GetSheets() Dim fso As Object
Dim folder As Object
Dim subfolders As Object
Dim MyFile As String
Dim wb As Workbook
Dim CurrFile As Object


With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Main Folder")
Set subfolders = folder.subfolders
MyFile = "c001.CSV"

For Each subfolders In subfolders

Set CurrFile = subfolders.Files

For Each CurrFile In CurrFile
Workbooks.Open Filename:=subfolders & "" & MyFile, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(MyFile).Close


Next

Next

Set fso = Nothing
Set folder = Nothing
Set subfolders = Nothing


With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With


End Sub</sub>

 

[ZLeonhart]

Been working this for a few hours and still can't get it to stop looping.

 

[Jerry Sullivan]

Welcome to MrExcel,

The endless loop is due to errors in your For Each statements. For example:

 For Each subfolders In subfolders

 For Each CurrFile In CurrFile

The syntax needed is:

For Each (SingleObject) in (CollectionOfThoseObjects)

It won't work if you use the same variable for the single object and the collection. You'll need to declare additional variables and do something like this...

 For Each subfolder In subfolders

 For Each CurrFile In CurrFiles

After correcting the syntax, there's still another problem with code. It will only look at files in the top level of subfolders, and won't look into files that are in subfolders of those subfolders. You can accomplish that objective with a recursive procedure that calls itself. (Your GetSheets example doesn't have any calls to GetSheets so while it has looping, it's not a recursive procedure).

John_w has a simple example of a recursive procedure to step through all subfolders at this link...

https://www.mrexcel.com/forum/excel...folders-my-folders-file-loop.html#post2087158