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

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





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

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23mHNv7lNGHA.3832@tk2msftngp13.phx.gbl...
> 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?