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