Walking Through Many Subdirectories, Many Workbooks, Many Worksheets

[markes1]

Hello, I need to copy a specific sheet (CONSTANT gPA) from multiple workbooks in multiple subdirectories and paste (special) that sheet to the workbook where my code is located (which is at the root, C:\2010 Budget\).

The code below walks through these subdirectories well enough, but it does not "Open" any of the workbooks. Here is the line/code for opening the workbook:



Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)


To give more context here is a larger code snippet:



For i = 1 To 50
If FileOrDirExists("C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator) Then
With Application.FileSearch
.NewSearch
.LookIn = "C:\2010 BUDGET\" & stateHold(i) & Application.PathSeparator
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls"
If .Execute() > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
If IsNumeric(Mid(wbResults.Name, 3, 4)) Then
For Each wks In Worksheets
Select Case wks.Name
Case gPA
wks.Unprotect
wks.Copy
Workbooks("2010 BUDGET TEMPLATE-ACCT-TEST.xls").Activate
Sheets.Add.Name = wbResults.Name & " - GPA"
Sheets(wbResults.Name & " - GPA").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Select
Next wks
End If
Workbooks(wbResults.Name).Activate
wbResults.Close SaveChanges:=True
Next lCount
End If
End With
End If
Next i


I am using Excel '07 on Vista, and I have tried to run this macro as both a '07 and '03 xls. Where do you think I am going wrong here?

Thanks in advance.

[Bob Phillips]

On scanning, the code looks okay.

Are you using Excel 2007, because if so, xlsx, xlsm, xlam, xlsb file types are NOT filetype msoFileTypeExcelWorkbooks.

[markes1]

thanks for that heads-up; however, everything is .xls....

[Tinbendr]

Just grabbing at straws here but, is gPA the Name of the sheet, or is it a fixed variable for the name of the sheet? (I see the forementioned CONSTANT gPA.)

If the first, then should your case statement be;

Case "gPA"

instead of

Case gPA

Option 2: (And I'm sure you're aware of this...)
Put in some indicators to see what is happening.


Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Application.statusbar = .foundfiles(lCount)
If IsNumeric(Mid(wbResults.Name, 3, 4)) Then
For Each wks In Worksheets
Application.statusbar = .foundfiles(lCount) & " " & wks.Name
Select Case wks.Name
Case gPA
...

Statusbar is not foolproof, as it routinely stalls when things get too complicated, but it's a start.