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

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