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

Option Explicit
Sub ReorgDataV5()
' stanleydgromjr, 03/25/2011
' http://www.excelforum.com/excel-general/768143-index-match-to-reformat-data.html
' ***** The row containg Total has been changed.  Row 2 is now clear to find LC for a date. *****
Dim ws As Worksheet, wF As Worksheet
Dim LR As Long, LC As Long, NR As Long, a As Long, aa As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF('Final Format'!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Final Format"
Set wF = Worksheets("Final Format")
wF.UsedRange.Clear
wF.Range("A1:F1") = [{"Date","Employee Name","Function","Quantity","Hours","AVG."}]
For Each ws In ThisWorkbook.Worksheets
  If Left(ws.Name, 7) = "Tracker" Then
    With ws
      LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
      LC = ws.Cells(2, Columns.Count).End(xlToLeft).Column
      For a = 4 To LC Step 3
        For aa = 4 To LR Step 1
          If Application.Count(ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2))) > 0 Then
            NR = wF.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            wF.Cells(NR, 1).Value = ws.Cells(2, a).Value
            wF.Range("B" & NR).Resize(, 2).Value = ws.Range("A" & aa & ":B" & aa).Value
            wF.Range("D" & NR & ":F" & NR).Value = ws.Range(ws.Cells(aa, a), ws.Cells(aa, a + 2)).Value
          End If
        Next aa
      Next a
    End With
  End If
Next ws
LR = wF.Cells(Rows.Count, 1).End(xlUp).Row
wF.Range("A2:A" & LR).NumberFormat = "m/d/yyyy"
wF.Range("E2:F" & LR).NumberFormat = "0.00"
wF.Range("D2:F" & LR).HorizontalAlignment = xlCenter
wF.UsedRange.Columns.AutoFit
wF.Activate
Application.ScreenUpdating = True
End Sub

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:

Sub snb()
  c00="E:\company\"
  c01="filename"
  c02=dir(c00 & c01 & "*.xls")

  do until c02=""
    with getobject(c00 & c02)
      for each sh in .sheets
        if lcase(left(sh.name,7))="tracker" then thisworkbook.sheets("master").cells(rows.count,1).end(xlup).offset(1).resize(sh.usedrange.rows.count,sh.usedrange.columns.count)=sh.usedrange.value
      next
      .close false
    end with
    c02=dir
  loop
End Sub

if you have 6 different folders:

Sub snb()
  sq=split("E:\company\|D:\folder2\|F:\folder3\|E:\company\folder4\|C:\folder5\|C:\folder5\folder6\","|")
  c01="filename"

  for j=0 to ubound(sq)
    c02=dir(sq(j) & c01 & "*.xls")

    do until c02=""
      with getobject(sq(j) & c02)
        for each sh in .sheets
          if lcase(left(sh.name,7))="tracker" then thisworkbook.sheets("master").cells(rows.count,1).end(xlup).offset(1).resize(sh.usedrange.rows.count,sh.usedrange.columns.count)=sh.usedrange.value
        next
        .close false
      end with
      c02=dir
    loop
  next
End Sub

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:

Sub snb()
  sq = Split("0:\company\day shift\a|0:\company\day shift\b|0:\company\day shift\c|0:\company\night shift\a|0:\company\night shift\b|0:\company\night shift\c", "|")

ok but for the part that says

c01 = "filename"

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?

For Each sh In .Sheets
          If LCase(Left(sh.Name, 7)) = "tracker" Then ThisWorkbook.Sheets("master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(sh.UsedRange.Rows.Count, sh.UsedRange.Columns.Count) = sh.UsedRange.Value

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.."

Sub snb()
  sq=split("E:\company\|D:\folder2\|F:\folder3\|E:\company\folder4\|C:\folder5\|C:\folder5\folder6\","|")
  c01="Tacker"

  for j=0 to ubound(sq)
    c02=dir(sq(j) & c01 & "*.xls")

    do until c02=""
      with getobject(sq(j) & c02)
        for each sh in .sheets
          if lcase(left(sh.name,7))="tracker" then thisworkbook.sheets("master").cells(rows.count,1).end(xlup).offset(1).resize(sh.usedrange.rows.count,sh.usedrange.columns.count)=sh.usedrange.value
        next
        .close false
      end with
      c02=dir
    loop
  next
End Sub

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 Stacie...I'm glad to help if i can...be back to you.

Hi Staci

I've included this code in the attached

Option Explicit
'Reference needs to be set to Microsoft Scripting Runtime
'Tools -> References ->Microsoft Scripting Runtime
Sub Find_Files()
    Application.ScreenUpdating = False
    Dim FSO As Scripting.FileSystemObject
    Set FSO = New Scripting.FileSystemObject
    On Error Resume Next
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Source Files"
    On Error GoTo 0
 
    FindFilesInAllFolders "C:\Company", True    'Change this line
    Set FSO = Nothing
    Call ReorgDataV5
    Application.ScreenUpdating = True
End Sub
 
 
'http://www.excelforum.com/excel-programming/764746-exclude-files-from-getopenfilename-based-on-file-name.html#post2474970
'Written: March 24, 2010
'Author:  Leith Ross
'Summary: List all files in a single folder or all files in the subfolders of the parent
'         folder on the ActiveSheet. The file name, date and time it was last modified
'         are listed in column "A:B" starting at row 1.
'Adapted by jaslake 03/29/2011
Sub FindFilesInAllFolders(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim SubFolder As Object
    Dim FileItem As Object
    Dim LR As Long
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        If FileItem.Name Like "Tracker*" And InStr(FileItem.Name, ".xls") And Not FileItem.Name = "Tracker Master.xls" Then
            With Sheets("Source Files")
                LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                .Cells(LR, "A") = SourceFolder & "\" & FileItem.Name
                .Cells(LR, "B") = FileItem.Name
            End With
        End If
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            FindFilesInAllFolders SubFolder.Path, True
        Next SubFolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

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

 FindFilesInAllFolders "C:\Company", True    'Change this line

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.