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,
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