CODEWITHSUNDEEP
vbaexcel
Jonathan
Jonathan

Reputation: 238

Excel VBA: Search for a directory

In vba, I would like to search through directories for a specific directory name. Ideally the searching time would be fast (similar from a windows search).

from different sources, I could build a script (given bellow) with a recursive sub program. The script works but it is very slow as soon as the hierarchy is a little complex.

Is there a way to make the search faster?

Sub GetFolder(Folder As String, searchF As String, colFolder As Collection)
Dim SubFolder, subF As New Collection, sf As String
If Right(Folder, 1) <> "\" Then Folder = Folder & "\"

If Dir(Folder & searchF, vbDirectory) <> "" Then colFolder.Add Folder & searchF & "\"

sf = Dir(Folder, vbDirectory)
Do While Len(sf) > 0
    If sf <> "." And sf <> ".." Then
        If (GetAttr(Folder & sf) And vbDirectory) <> 0 Then
                subF.Add Folder & sf
        End If
    End If
    sf = Dir()
Loop

For Each SubFolder In subF
    GetFolder CStr(SubFolder), searchF, colFolder
Next
End Sub

Upvotes: 1

Views: 3376

Answers (1)

Vityata
Vityata

Reputation: 43595

I think you are underestimating the hierarchy size. Change your code to this one to see how many folders you are recursing through.

Option Explicit

Sub GetFolder(Folder As String, searchF As String, colFolder As Collection, ByRef counter As Long)

    Dim SubFolder, subF As New Collection, sf As String
    If Right(Folder, 1) <> "\" Then Folder = Folder & "\"

    If Dir(Folder & searchF, vbDirectory) <> "" Then colFolder.Add Folder & searchF & "\"

    sf = Dir(Folder, vbDirectory)

    Do While Len(sf) > 0
        If sf <> "." And sf <> ".." Then
            If (GetAttr(Folder & sf) And vbDirectory) <> 0 Then
                Debug.Print Folder & sf
                counter = counter + 1
                subF.Add Folder & sf
            End If
        End If
        sf = Dir()
    Loop

    For Each SubFolder In subF
        GetFolder CStr(SubFolder), searchF, colFolder, counter
    Next

End Sub

Public Sub TestMe()

    Dim newC        As New Collection
    Dim colChecked  As New Collection
    Dim counter     As Long

    GetFolder "C:\Users\<username>\Desktop\BA Tools", "v", newC, counter
    Debug.Print counter

End Sub

What is the number that you get at the end of the code, when you run it?

Upvotes: 2

Related Questions