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

[ikr]

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

 

[Tom Ogilvy]

check this code posted in the past by Bill Manville:

Dim aFiles() As String, iFile As Integer


Sub ListAllFilesInDirectoryStructure()
iFile = 0
ListFilesInDirectory "D:\TEMP\" ' change the top level as you wish
MsgBox iFile & " files found"
End Sub


Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Integer, stFile As String


' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' note that Dir returns files as well as directories when vbDirectory
specified
iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile <> Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(1 To iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
iFile = iFile + 1
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
End If
stFile = Directory & Dir()
Loop


' now, for any directories in aDirs call self recursively
If iDir > 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub


--
Bill Manville
Oxford, England
Microsoft MVP - Excel

 

[Bob Phillips]

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]

check this code posted in the past by Bill Manville:

Dim aFiles() As String, iFile As Integer


Sub ListAllFilesInDirectoryStructure()
iFile = 0
ListFilesInDirectory "D:\TEMP\" ' change the top level as you wish
MsgBox iFile & " files found"
End Sub


Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Integer, stFile As String


' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' note that Dir returns files as well as directories when vbDirectory
specified
iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile <> Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(1 To iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
iFile = iFile + 1
ReDim Preserve aFiles(1 To iFile)
aFiles(iFile) = stFile
End If
stFile = Directory & Dir()
Loop


' now, for any directories in aDirs call self recursively
If iDir > 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub


--
Bill Manville
Oxford, England
Microsoft MVP - Excel

--

Regards,

Tom Ogilvy

Thanks for this, Tom (& Bill). I take that the array aDirs contains the list
of my workbooks (where each array member is the string containing the
filename with full path), so that I need to cycle through this array and
perform my code on each member?

 

[ikr]

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

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

 

[Bob Phillips]

news:uOf%[email protected]...

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.

 

[ikr]

That explains it perfectly - thanks again, Bob.