Searching For File Strings In Folders Is Too Broad

[Ark68]

I am using this function to check whether a file exists in a particular folder.

Function FileExistsWithPartialName(FolderPath As String, partialName As String) As Boolean
    Dim fileName As String
    Dim exists As Boolean
    exists = False
    
    ' Ensure folder path ends with a backslash
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If
    
    ' Use Dir to search for files matching the partial name
    fileName = Dir(FolderPath & "*" & partialName & "*")
    
    ' If a match is found, Dir returns the file name
    If fileName <> "" Then
        exists = True
    End If
    
    ' Return the result
    FileExistsWithPartialName = exists
End Function

Assume FolderPath = "M:\Scenic Posters\Scenery\Mountains\" and partialName = "Deluge".

In my testing, that folder does not contain a file "Deluge", however, does include "Deluge Restoration" This code flags the file "Deluge" as existing, when it really doesn't. How can I improve my code to exlude files names in which a single word may exist in a string. eg Water Deluge, Deluge Restoration. I have a feeling the searches for basic single words could be found found in file names such a single word within a greater string.

 

[Micron]

You're using wildcards on both sides of the string, so you're going to get any partial matches. Putting * on only one side of the string probably won't work either. I doubt you can use other wildcards like ? since that replaces a single character so I can only see you finding " . . .\Deluge.jpg" if you pass "Deluge.pdf" to the sub and drop the wildcard characters. If there is a way to do this with Regular Expressions (RegEx) I wouldn't be able to figure it out.

 

[Ark68]

Thanks Micon, as I contemplated the code, I realized that I can't have the best of both worlds so to speak. I went back and looked at my data and realized that all the files had a pattern.

some text - partialName.ext

some text and ext are variable hence the wildcards. So I changed the contents of partial name to all a preceding "- " and a trailing ".".

partialName = "- " & partialName & "."

I think this will capture the difference between "Deluge" ("- Deluge."), from "Water Deluge" ("- Water Deluge.") and "Deluge Restoration" ("- Deluge Restoration.")[/code]

 

[Micron]

If what you ended up with doesn't always work and if the goal is that you want ". . . \Deluge.jpg" and not ". . . Water Deluge.jpg" then maybe:
- find the last \ using InstrRev() and add 1. I'll call that strStart
- find the last dot using InstrRev(). I'll call that strEnd and the word being looked for (e.g. strIn)
- use Mid() to get the string between strStart and strEnd. If it a) equals the length of strIn, or b) = strIn then you know the file name is Deluge and nothing else.

Given some of the worksheet functions I've seen to solve issues I imagine someone could write a formula that could handle that. Or as I mentioned, perhaps RegEx. Unfortunately that's not me.

EDIT - on second thought, it might be possible that a file name (before the dot) is the same length as strIn but is not the same word, so I guess = would be the way to go, not the length.

 

[jimrward]

FilenameRoot=PartialName then bingo
Does this approach not severely limit your searching ability as you will have to be very explicit with your partial name string

 

[Ark68]

Thanks Micon ... I think your suggestion is worthy of experimentation, but some time in the future as I need to continue to advance (knowing of course that my code may likely fail in actual non static testing use). If I remember, I'll post my success.

@jimrward, thank you for your suggestion. I'm not really sure how to interpret it though. I Googled FilenameRoot to see if iot was a VBA function or something, but couldn't find much information that I felt was useful to me in this instance. Happy to consider anything you can add about it.

Does this approach not severely limit your searching ability as you will have to be very explicit with your partial name string

Which approach are you referring? Post 3 (mine), 4 (Micron's) or your's in post 5 if you're suggesting a possible approach.