CODEWITHSUNDEEP
ms-accessvba
Eddy
Eddy

Reputation: 105

How to stop recursive search once the file is found in VBA

i have the below script , modify from this http://www.vbforums.com/showthread.php?613400-Loop-through-folders-subfolders

Private Sub Command1_Click()

Dim fld As Folder
Dim searchString As String
Dim ResultFilePath As String
Set fso = New FileSystemObject
Set fld = fso.GetFolder("C:\Users\janedoe\Desktop\jane")   
searchString = "ClaimSheet.xlsx"                       

ResultFilePath = RecursiveSearch(fld, searchString)    
Set fld = Nothing
Set fso = Nothing

If ResultFilePath = "" Then
    MsgBox ("We could not find the file " & searchString)
Else
    MsgBox ("We found it, its at " & ResultFilePath)
End If

End Sub

Function RecursiveSearch(fld As Folder, search As String) As String
Dim tfold As Folder
Dim tfil As File

For Each tfold In fld.SubFolders
    Debug.Print "looking in the  " & tfold & " folder"
    RecursiveSearch tfold, search

    If RecursiveSearch = search Then
        Exit Function
    End If

Next


Debug.Assert InStr(tfil, search) = 0

    If InStr(tfil.Name, search) Then           

        RecursiveSearch = tfil.Path
        Exit function
    End If
Next


End Function

What I would like the RecursiveSearch function to do is, search the folder for the searchString file, once found, stop searching and return the file path.

The problem is, I can't exit function without losing the value at the line

RecursiveSearch = tfil.Path

I think it does that because the function might have gone out of scope when returning the upper level .

Any help would be appreciated,

Upvotes: 0

Views: 996

Answers (1)

Andrew Cooper
Andrew Cooper

Reputation: 32576

Your recursion line should be:

RecursiveSearch = RecursiveSearch(tfold, search)

This will allow each level of the recursion to pass it's result back up the chain.

Also, the following line seems to be missing from the code in your question:

For Each tfil In fld.Files

Upvotes: 2

Related Questions