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