Unexpected Results Of Finding A String Within A String When Seaching for Files (VBA)

[Ark68]

I have this function which scours all the folders on a drive looking for a a file that contains a particular string (a substring of the file name). Example: To find the file "Sahara - Fine Grantite.docx", the variable SearchFileName would be "- Fine Granite." . This excludes any text before the hyphen, and after the period. (all filenames in the directory have the same format "text - text.ext"). The directory path of the individuals files follows this pattern:
O:\Science\{x}\{y}\{files} where {X} and {Y} are variable.

This code is not finding the right file. It's stopping the search at the first file directory {Y}. I think I have my InStr code wrong, but am at a brain lapse to figure out the error. For example, the file "Sahara - Fine Grantite.docx" is stored in O:\Science\S\Sahara\. SearchFileName = "- Fine Granite.". sfResult returns a value of "O:\Science\A\Andretti\Andrettit - Amethyst.docx"

Function SearchInFolder(Folder As Object, SearchFileName As String) As String
    Dim SubFolder As Object
    Dim File As Object
    'Stop 'SearchFileName
    On Error Resume Next
    'Stop 'searchfilename
    ' Loop through each file in the current folder
    For Each File In Folder.Files
        'Debug.Print Folder
        If Err.Number = 70 Then
            Err.Clear
            Exit For
        End If
        'If StrComp(File.Name, SearchFileName, vbTextCompare) = 0 Then
        If InStr(File.Name, SearchFileName, vbTextCompare) <> 0 Then  'changing this to = 0 nets the same results
            SearchInFolder = File.Path
            Exit Function
        End If
    Next File
    
    ' Loop through each subfolder
    For Each SubFolder In Folder.SubFolders
        Debug.Print SubFolder
        If Err.Number = 70 Then
            Err.Clear
            Exit For
        End If
        SearchInFolder = SearchInFolder(SubFolder, SearchFileName)
        If SearchInFolder <> "File not found" Then Exit Function
    Next SubFolder

 

[Logit]

Untested here ... this is a "wild stab in the night" for me :

Function SearchInFolder(Folder As Object, SearchFileName As String) As String
    Dim SubFolder As Object
    Dim File As Object
    Dim Result As String
    
    On Error Resume Next
    
    ' Loop through each file in the current folder
    For Each File In Folder.Files
        If Err.Number = 70 Then
            Err.Clear
            Exit For
        End If
        If InStr(File.Name, SearchFileName, vbTextCompare) <> 0 Then
            SearchInFolder = File.Path
            Exit Function
        End If
    Next File
    
    ' Loop through each subfolder
    For Each SubFolder In Folder.SubFolders
        If Err.Number = 70 Then
            Err.Clear
            Exit For
        End If
        Result = SearchInFolder(SubFolder, SearchFileName)
        If Result <> "" Then
            SearchInFolder = Result
            Exit Function
        End If
    Next SubFolder
    
    ' If no file is found
    SearchInFolder = "File not found"
End Function

 

[John_w]

I think I have my InStr code wrong

Yes, it's wrong because the start argument is required if the compare argument is specified. Change it to:

        If InStr(1, File.Name, SearchFileName, vbTextCompare) > 0 Then

Also, what error is the On Error Resume Next and If Err.Number = 70 Then meant to trap?

 

[Ark68]

Thank you both for your suggestions. Both are appreciated.
I do have an additional question related to this. For the most part, the files are being found. One exception that I have found in my testing is when SearchFileName = "- 100% Organic 1." I suspect the special character (%) is contributing to that failure. It is quite possible that file names will have permitted special characters in them like %, _, &, $ .

Also, what error is the On Error Resume Next and If Err.Number = 70 Then meant to trap?

It traps folders that err because of "Permission denied". I was running into this before I changed the folder value in the code calling this from "O:\" to "O:\Science". It may be redundant now.

 

[Logit]

Ark68​

Please advise if my solution worked for you. Thank you.

 

[Ark68]

Hello Logit, I'm sorry, I hadn't checked it yet. I will try it when I get to that point again.

 

[Logit]

Thanks

 

[Ark68]

Logit, I tried your suggestion and it did not work. It's providing the same results as in my original post. Needless to say the effort was appreciated.

 

[Logit]

Thanks for the reply.