Loop Over Multiple Directories Not Working!!!!

**sanjeevpandey** · 03-01-2013, 09:00 AM

Hi,

I have a code through which I'm trying to loop certain commands over several directories one by one. It merges the data from all the excel files present in subfolder into a summary workbook, then save it with subfolder name and close the summary workbook, and move to next subfolder.

However, the code is giving error in the first declaration, when I use private sub to take commands from the public sub. I don't quite understand that given my limited knowledge in excel VBA. I really need help with this to continue on my work. Can you point out the mistake in the code or a blunder or may be propose an improvement?

Here is the public sub commande:

Please Login or Register  to view this content.

Here is the rest of the code:

Please Login or Register  to view this content.

**AlphaFrog** · 03-01-2013, 10:16 AM

Do you have a reference set to the Scripting Runtime library?

From the VBA menu:

Select Tools\References
Select Microsoft Scripting Runtime

**sanjeevpandey** · 03-01-2013, 10:39 AM

Thanks AlphaFrog for your reply!

I did activate Microsoft Scripting Runtime following your comment, and it did not give any error, but it did not run the code either??

What could be the bug in this case? I suppose its with the subfolder looping part where its having no clue, because the code is working, generally without the subfolder loop part!

What you think?

**sanjeevpandey** · 03-01-2013, 10:55 AM

Its giving "Table Awaited" error with this corrected code:

Please Login or Register  to view this content.

What does that mean? It couldn't fill in the array of files from the subfolder?

**AlphaFrog** · 03-01-2013, 11:12 AM

Originally Posted by sanjeevpandey

and it did not give any error, but it did not run the code either??

...because the code is working, generally without the subfolder loop part!

What you think?

I think I'm confused. It didn't run the code because the code is working????

Originally Posted by sanjeevpandey

Its giving "Table Awaited" error with this corrected code:

You changed the code but don't say what you changed. I think you over estimate my ability to help.

**sanjeevpandey** · 03-01-2013, 11:17 AM

Hi AlphaFrog,

I did not overestimate anything...since I told you I'm not expert at VBA, so I commit silly mistakes sometimes! At the beginning I changed MyFiles declaration from MyFiles () to MyFiles, I didn't understand it meant an array! I made just that change!

Like I said the code works if I specify a folder and run the macro from folder to folder myself, I wanted to make it automatic and loop over folders itself! Here is the code that is working well for a specified folder:

Please Login or Register  to view this content.

It copies data from all the excel files present in the directory to a summary workbook and then saves the summary workbook with current date.

**AlphaFrog** · 03-01-2013, 11:25 AM

FilesInPath = Dir(Subfolder & "\" & "*.xlsx*")

**sanjeevpandey** · 03-01-2013, 11:51 AM

Thanks! This modification from yours got the code started, but its only saving the summary workbook using the subfolder name without copying the data from the excel files present in the subfolder? I didn't receive any errors or warning, it just skipped the central part of the code! How its possible?