Recursive directory listing

[Jim]

Is it possible to perform a recursive directory listing using VBA ? I can
manage to go down one level but am struggling to code for (more than one)
recursion, i.e. all files in all sub directories of a directory.

Regards, Jim.

 

[Norman Jones]

Hi Jim,

The following was posted by Roger Hammond to this NG earlier this year::

<<<----------------------------------------------------- START

this will spit out the contents of a folder and sub folders for either a
given file type or all types to a new sheet.

Option Explicit

Sub FullDir()
ActiveWorkbook.Sheets.Add
GetFiles "c:\my documents\my excel files\xspandxl\", ".xls"
End Sub

Sub GetFiles(strRootDir As String, Optional strType As String)
Dim strDirName As String
Dim bTypeMatch As Boolean
Dim colDirs As Collection
Dim lDirCounter As Long
Dim lIndex As Long

Set colDirs = New Collection
colDirs.Add strRootDir
lDirCounter = 1
lIndex = 1

'check for sub directories and make a recursive call to the lowest level
dirs first

Do While lDirCounter <= colDirs.Count
strRootDir = colDirs(lDirCounter)
strDirName = Dir(strRootDir, vbDirectory + vbNormal)
Do While strDirName <> ""
If strDirName <> "." And strDirName <> ".." Then
If (GetAttr(strRootDir & strDirName) And vbDirectory) =
vbDirectory Then
'add to the directories collection so that this will be done
later
colDirs.Add strRootDir & strDirName & "\"
Else
'we found a normal file
bTypeMatch = False
If strType = "*.*" Then
bTypeMatch = True
ElseIf UCase(Right(strDirName, Len(strType))) =
UCase(strType) Then
bTypeMatch = True
End If
If bTypeMatch = True Then
'we found a valid file
Cells(lIndex, 1) = strRootDir & strDirName
lIndex = lIndex + 1
End If
End If
End If
strDirName = Dir
Loop
lDirCounter = lDirCounter + 1
Loop
End Sub

Robin Hammond
www.enhanceddatasystems.com

<<<----------------------------------------------------- END

 

[Myrna Larson]

Here's some similar code that Bill Manville posted (several) years ago. It
uses standard arrays rather than collections. The former is faster, I believe.
The first Sub, ListAllFilesInDirectoryStructure() is a demo/test.

Option Base 1
Dim aFiles() As String, iFile As Integer

Sub ListAllFilesInDirectoryStructure()
Dim Counter As Integer
iFile = 0
ListFilesInDirectory "c:\test\" ' change the top level as you wish

For Counter = 1 To iFile
Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter)
Next

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) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
iFile = iFile + 1
ReDim Preserve aFiles(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