Retrieve data from numerous workbooks inside a Master File

I would like a macro to loop through a master file, and retreiving specific data. To make it easy: I am going to generically name all my files, but in real life they are named something else. And they all have something similliar about them, as you will see.

Ok so I have a master folder called:
for our purposes we will call it Company, then inside I have it seperated into Manager, Day Shift and Night Shift, and then inside each "Shift" files, I have 3 seperate folders we can call "A", "B", & "C", and then inside each one of those folders is a file that starts with "Tracker"...(so there is 6 of them all together, 3 in each shift). And finally inside those "Tracker' workbooks, I have worksheets that start with "Tracker"Would it be possible to have a macro built into a workbook named "Scorecarding" into a workbook named "Tracker-Master"worksheet named "Tracker-Master" in the Manager folder, to bring the data from all the different sources?

might sound a little complicated but with the help of stanleydgromjr, I have a macro that does function how I want, but only per one workbook, I would just like to loop through all the files.
http://www.excelforum.com/excel-gene...rmat-data.html

*please note: we have since modified the Macro, below is the "Final" Macro that works 100% for all the files

Please Login or Register  to view this content.

I am not sure how to loop between all the folders, can someone assist or point me in the correct direction.

Please view the attachments, as it does provide all of the above data, in a better view, I am not sure how else I can make it make sence so I will enclose a sample workbook (ReOrgDatafv1), so you can see the tasks that it performs.


Any thoughts, ideas are appreciated...

Thanks
-Staci

You will find an example of some code which will loop through a folder tree of any complexity on my download page. You might wish to incorporate the principles on which it is built (recursion) to solve your particular challenge.

Hope this helps.

The basic code structure for what you want if all files reside in the same folder:

Please Login or Register  to view this content.

if you have 6 different folders:

Please Login or Register  to view this content.

http://homepage.ntlworld.com/martin.rice1/book1.xls

mrice, not sure how to decipher the code...

regarding SNB's code...ok so changing SNB's code to work in my example scenerio:

Please Login or Register  to view this content.

ok but for the part that says

Please Login or Register  to view this content.

each file is named different but each start with "tracker" so I am not sure what to put there? &/or what file is it asking for?

also, what does this part of the code do?

Please Login or Register  to view this content.

and where do I put the file path to where I want to put the data? The "tracker-master" file
as there are 6 worksheets to be sourced to one spot, so 7 worksheets altogether.

I am sorry for my ignorance, I am just learning the basics, and this is over my head.

If the filenames start with "tacker.."

Please Login or Register  to view this content.

But what are the sheetnames ?

Are the file paths too complicated?


I think I might have confused you. Each of the "Tracker" folders are in different sub folders, so there is 7 file paths that are different.

Each file will basically look like this:

o:drive\Company File\"shift"\"Department"\"Tracker-" file

where the shift & department names changing based upon what tracker it is for...

Hope that helps clears up any confusion
-Staci

Maybe it's better to reorganize all those files and put them in one folder.
You can distinguish them by their filenames.

Hi Staci

I've looked at your attachments and, while they can probably most certainly be resolved by shorthand, with the fewest lines of code required, neither you nor I would likely be able to read them...and, unless the contributor has told you so, assume they've been untested.

Some contributors offer suggestions...not solutions...it's up to you to try to adapt their "suggested, untested" code to your environment. Of course, if it's untested AND you can't read it AND it doesn't work because of errors, it's difficult to fix it unless you CAN read it...ah well...

If your issue hasn't been resolved by tomorrow, I'll look at it. Can't guarantee results...can't guarantee short code...if I can find code that works, I CAN tell you it's been tested and the code works on the files I've been presented with.

While your tree structure is somewhat complex and may require further explanation, I'll work on my understanding of it.

I'll follow the Thread and get back to you.

Hi Stacie...I'm glad to help if i can...be back to you.

Hi Staci

I've included this code in the attached

Please Login or Register  to view this content.

I've also modified ReorgDataV5 to work with all the Tracker folders in the various subfolders.
Please note you'll need to set a reference to Microsoft Scripting Runtime and you'll need to change this line of code

Please Login or Register  to view this content.

The code has been tested in Excel 2000 and 2007 and it assumes the directory tree structure you described. Assuming I understand your issue, this should work for you. Let me know of issues.

Hi Staci

I just reran the procedure in 2007 and it worked as expected. If you wish, do this. I've attached two .jpg's. I'd like you to set some breakpoints in the procedures. Take a look at the pictures...do F5 where I indicated...if it proceeds w/o locking up, do the next F5...when you get to the second .jpg, step through the code (F8)...I'd like to know why it's breaking...running fine here.

If you have time, get back to me.