Search filesystem for filepath using VBA

Question

I am looking for a way to search a specific folder for a subfolder containing a certain string. Below I have listed a function I typed off the top of my head to see if it would work. Well, it does work but when I am talking about searching through 6,000 folders on a network drive it just isn't fast enough.

I'm sure that there is a better way to do it but I can't seem to dig anything up on Google.

1. Is there an object that allows me to leverage the windows built in file system searching and indexing capabilities?

2. As an alternative, does someone have a way to optimize my code? The main bottleneck is the usage of instr.

Here is the code:

Function findPath(strId As String) As String
checkObj
Dim strBase As String
strBase = opt.photoBasePath

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim baseFolder As Object
Set baseFolder = fs.getfolder(strBase)

Dim folder As Object

For Each folder In baseFolder.subfolders
    If InStr(1, folder.name, strId) > 0 Then
        findPath = strBase & "\" & folder.name
        Exit Function
    End If
Next folder


End Function

P.S. I'm sure someone will suggest modifying my folder structure so that I can programmatically predict the path but for various reason that isn't possible in my case.

Answer 1

Consider splitting traversing the folders from finding your key. Instead of the instr test, store the folder names in a table, then use a query on the table to find your target. It might be slower, but searching should be faster.

Answer 2

You could use the FindFirstFile Win32 API, which allows you to search for files or sudirectories matching a specified name. Additionally, you could also use the FindFirstFileEx function, along with a FINDEX_SEARCH_OPS parameter of FindExSearchLimitToDirectories, which would limit your search to a file that matches a specified name and is also a directory (if the file system supports directory filtering). For more information on using these functions from VB/VBA see the following:

http://www.xtremevbtalk.com/showpost.php?p=1157418&postcount=4

http://support.microsoft.com/kb/185476

http://www.ask-4it.com/how-to-use-findfirstfile-win32-api-from-visual-basic-code-2-ca.html