Merge all excel files from each sub folder

Hello,

I need your help guys.
I have a master folder named 2022, location (C:\Users\Username\Desktop\2022), in this master folder there are multiple sub folders, in each sub folder there are multiple excel files. I need a VBA code to merge all excel files from each sub folder. A merged file should be created for each sub folder and each merged file should contain only one sheet. Data must be collected only from sheet 1 from each excel file.
My work so far:

Please Login or Register  to view this content.

The code partially works, first merged file for first sub folder is created but when it should go to the next one, the excel application closes without a single error message.
Any help will be greatly appreciated.

Regards

alternative to VBA

https://www.youtube.com/watch?v=OFt9b6DgvY0

Originally Posted by zGod

but when it should go to the next one, the excel application closes

Hello,

your logic on paper is correct but not according to how VBA works as your second Dir cancels the previous !

So first follow Alan's answer above.

If for some obscure reason you wanna stay on VBA side that needs to create a recursive procedure.
If it's under Windows only and on a SSD drive
- or if you don't mind that may run six times slower than Dir on a mechanical hard drive -
an easier way is to use the Windows ActiveX FileSystemObject like this thread …
(Even both Dir & FSO can be combined in your case.)
If for MAC or with a mechanical hard drive the recursive procedure must use Dir VBA function, I'll share another sample link …

Edit : maybe that no needs any recursive procedure if there is only a single level of subfolders in the master '2022' folder like
[…]\2022\Folder1
[…]\2022\Folder2

so not multiple sub levels like
[…]\2022\Folder1
[…]\2022\Folder1\Folder2
[…]\2022\Folder1\Folder3
[…]\2022\Folder1\Folder3\Folder4
[…]\2022\Folder5
[…]\2022\Folder5\Folder6

So if for some reason you wanna stay on VBA side is it a single sub level folders or multiple ?