VBA/VB Recursing Through Nested Directories

I wrote a post about iterating over a directory with a Dir loop awhile back and I just received an email asking about nested directories. I’m a big fan of nice, big flat directory structures, but I frequently run into nested data as well. Here’s a VB6 snippet to traverse a directory structure recursively. Keep in mind that there is a price to recursion because of the nesting. If you have massive amounts of deeply nested data, you may want to try a different approach. This has worked for me 99% of the time.

Quick Explanation starting from bottom.

The “ripSlashes” function simply appends on a “/” trailing forward slash if it is missing. The recurse function relies on it.

The “nestedDir” function takes three parameters: 

1. The first parameter is a variable you define in the subroutine or function that will call the nestedDir function. It is basically a holder for the data returned by the nestedDir function.
2. The second parameter is simply the root directory you wish to recurse.
3. The third parameter is the file extension you wish to grab.

Finally, I’ve got a small subroutine from an Excel Spreadsheet that simply writes out the full file paths into the first column of the spreadsheet. Hope this helps someone.

———————————

Sub getSomeFolders()
'get some data out of nested xml files and put into a spreadsheet, recursively

Dim colFiles As New Collection
Dim x As Integer
Dim vFile

nestedDir colFiles, "c:\scott\new files\", "*.xml"

For Each vFile In colFiles
   x = x + 1
   Sheet1.Cells(x, 1) = vFile 'full path to the files you are recursing
Next vFile

MsgBox "done"
End Sub

Public Function nestedDir(colFiles As Collection, sFolder As String, sFileType As String)
    Dim sTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in sFolder matching sFileType to colFiles
    sFolder = ripSlashes(sFolder)
    sTemp = Dir(sFolder & sFileType)
    Do While sTemp <> vbNullString
        colFiles.Add sFolder & sTemp
        sTemp = Dir
    Loop

    sTemp = Dir(sFolder, vbDirectory)
    Do While sTemp <> vbNullString
        If (sTemp <> ".") And (sTemp <> "..") Then
            If (GetAttr(sFolder & sTemp) And vbDirectory) <> 0 Then
                colFolders.Add sTemp
            End If
        End If
        sTemp = Dir
    Loop

    'Call nestedDir for each subfolder in colFolders
    For Each vFolderName In colFolders
        Call nestedDir(colFiles, sFolder & vFolderName, sFileType)
    Next vFolderName

End Function

Public Function ripSlashes(sFolder As String) As String
    If Len(sFolder) > 0 Then
        If Right(sFolder, 1) = "\" Then
            ripSlashes = sFolder
        Else
            ripSlashes = sFolder & "\"
        End If
    End If
End Function

Share on Facebook

Popularity: 10% [?]