opening workbooks in subfolders

[Sian]

My boss (who lies awake at night dreaming up these things) wants a workbook
in folder X which reads in the contents of all the (one sheet) workbooks in
sub-folders of X at the click of a button. Neither the sub-folder names nor
the names of the workbooks within them are constant.

I'm sure I'm not the only person who's had to do this and this is way beyond
my job description, so I'm writing in hope that if anyone has useful code
snippets, they'll post them.

Thanks in advance!
Sian

 

[Mike H]

Sian,

So let me be sure I understand. Your boss want you to open a workbook the
name of which you don't know that resides in a sub folder you don't know the
name of and in any case is liable to change. Now le me think!!!


Mike

 

[Sian]

Ok, obviously I didn't explain myself very well?
It would look like this: Folder X\summarysheet.xls

would read in info from
Folder X\Apples\appleslaunch.xls
Folder X\Pears\pearslaunch.xls
etc.

except that I don't know "Apples", "Pears", "appleslaunch.xls" etc. so I
guess I'd have to read all the subfolders into an array and then read in file
names for each array element...

 

[Joel]

Try this code. It copies every shett in every workbook

Sub GetWorksheets()

strFolder = ThisWorkbook.Path

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

Call GetWorksheetsSubFolder(strFolder + "\")
End Sub

Sub GetWorksheetsSubFolder(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count > 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetWorksheetsSubFolder(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200
For Each fl In folder.Files
If Right(UCase(fl.Name), 4) = ".XLS" Then
Workbooks.Open Filename:=strFolder & fl.Name
Set oldbk = ActiveWorkbook
For Each sht In oldbk.Sheets
With ThisWorkbook
sht.Copy after:=.Sheets(.Sheets.Count)
End With
Next sht
oldbk.Close
End If
Next fl

200 On Error GoTo 0

End Sub

 

[Sian]

This worked perfectly first time - THANK YOU!!!!