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

ZLeonhart

New Member
Joined
Apr 1, 2018
Messages
2
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 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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Welcome to MrExcel,

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

Code:
 For Each subfolders In subfolders

 For Each CurrFile In CurrFile

The syntax needed is:

Code:
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...

Code:
 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
 
Last edited:

Forum statistics

Threads
1,148,291
Messages
5,745,897
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top