Perform code on all (closed) workbooks scattered across sub-folders of common parent folder

Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
.... your code here on Activeworkbook
Activeworkbook.Close
End If
Next file

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ikr" <ik_._rip_@_ntlworld_._com> wrote in message
news:%23zyKHvlNGHA.3144@TK2MSFTNGP11.phx.gbl...
> I have some code that I want to be performed on all the closed workbooks
in
> sub-folders of a common parent, viz:
>
> parent/childfolder1/workbook1.xls
> parent/childfolder2/workbook2.xls
> parent/childfolder3/workbook3.xls
> parent/childfolder3/workbook4.xls
> parent/childfolder4/workbook5.xls
> .
> .
> .
> parent/childfolderX/workbookY.xls
>
> These aren't the names of the folders and workbooks, I've just used them
> here for illustration. Note that some of the childfolders contain more
than
> one workbook on which I'd like to perform the code.
>
> I've written the code, and it works fine, but at the moment I'm relying on
> the user to run it on each workbook in turn. The code opens the workbook
> chosen by the user via the Application.GetOpenFilename() method, performs
> the code on that workbook, and then closes that workbook. The user then
> moves onto the next workbook. Is there a way to get the code to run
> automatically on each workbook in every sub-folder of the <parent> folder
> via one overall procedure? Basically, I want the user to just have to
> specify the parent folder, and then VBA will do its stuff on all workbooks
> in all childfolders beneath the parent? This sounds like it should be
easy,
> but something tells me it's going to be very complicated!
>
> TIA
>
> Ian
>
>

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uOf%23f9lNGHA.1288@TK2MSFTNGP09.phx.gbl...
> Dim oFSO
>
> Sub LoopFolders()
>
> Set oFSO = CreateObject("Scripting.FileSystemObject")
>
> selectFiles "c:\MyTest"
>
> Set oFSO = Nothing
>
> End Sub
>
>
> '---------------------------------------------------------------------------
> Sub selectFiles(sPath)
> '---------------------------------------------------------------------------
> Dim Folder As Object
> Dim Files As Object
> Dim file As Object
> Dim fldr
>
> Set Folder = oFSO.GetFolder(sPath)
>
> For Each fldr In Folder.Subfolders
> selectFiles fldr.Path
> Next fldr
>
> For Each file In Folder.Files
> If file.Type = "Microsoft Excel Worksheet" Then
> Workbooks.Open Filename:=file.Path
> .... your code here on Activeworkbook
> Activeworkbook.Close
> End If
> Next file
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips

Many thanks, Bob. I *think* I can sort of follow what's going on here. It
looks very elegant. The thing that's confusing me is that selectFiles
appears to call itself(?) Is this a technique that sort of recursively "goes
down through the multiple folder\subfolder structure to ultimately get at
the workbook files"? I'm confused at how it ultimately manages to cycle
through *all* the workbooks, though. Please could you provide some guidance?
Are the <Folder>, <Files>, <file> and <fldr> that you declare as Object,
some sort of "user-defined" objects? I assume that Subfolders is an
Excel-defined collection of Folder, since you didn't declare it or define it
via a SET statement? Hope I'm making sense.
Ian

"ikr" <ik_._rip_@_ntlworld_._com> wrote in message
news:Oi6BmomNGHA.1288@TK2MSFTNGP09.phx.gbl...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uOf%23f9lNGHA.1288@TK2MSFTNGP09.phx.gbl...

> Many thanks, Bob. I *think* I can sort of follow what's going on here. It
> looks very elegant. The thing that's confusing me is that selectFiles
> appears to call itself(?) Is this a technique that sort of recursively
"goes
> down through the multiple folder\subfolder structure to ultimately get at
> the workbook files"?

That is exactly it, it is recursive code. The recursion is being used so
that each level of subfolders will be processed, no matter how many there
are, and without knowing beforehand how many levels there are.

> I'm confused at how it ultimately manages to cycle
> through *all* the workbooks, though. Please could you provide some
guidance?

What it does is process every subfolder. First it checks if that suvfolder
has its own subfolders, if so it calls itself for each of thsoe subfolders,
and on return from the recursive call, it processes the workbooks in the
original subfolder. So if you have a structure like so

Level 1
Level 2
Level 3
level 4

it will go all the way down to level 4, process the workbooks there, and on
exit, return to level 3 where it will process the workbooks there, then
level2, etc.

> Are the <Folder>, <Files>, <file> and <fldr> that you declare as Object,
> some sort of "user-defined" objects? I assume that Subfolders is an
> Excel-defined collection of Folder, since you didn't declare it or define
it
> via a SET statement? Hope I'm making sense.

No I have just declared them as generic objects. I did this rather than the
specific FileSystem (FSO) object data types so that I didn't have to set a
reference to FSO, it is a bit simpler.

Subfolders is an FSO property, as is Files, whereas GetFolder is an FSO
method.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ulAVuonNGHA.3944@tk2msftngp13.phx.gbl...

That explains it perfectly - thanks again, Bob.