1

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.

Icode4food
  • 8,504
  • 16
  • 61
  • 93
  • Have you considered trying to use VB.NET with the entirely free Visual Basic .NET 2010 Express Edition? While I'm not 100% sure, I would think that .NET would have better performance than Access VBA. – Ben McCormack Oct 05 '10 at 16:48
  • Your code finds a single folder name which matches strId. Can there be more than one matching folder name? If so, do you want them all, or only the first match? – HansUp Oct 05 '10 at 18:04
  • @HansUp: There is a bit of a naming convention that will guarantee that I will only get one result. If there happens to be more than one match for some strange reason, I will just go with the first one. – Icode4food Oct 05 '10 at 18:28
  • @Ben McCormack: how does one use VB.NET in a Microsoft Access database application? – David-W-Fenton Oct 05 '10 at 21:29
  • @David I wasn't intending to suggest that he use VB.NET in a Microsoft Access database application, though I suppose it may be possible (if not painful) through a COM-callable .NET wrapper; it works with VB6 apps, at least. Based on the OP's first sentence, "I am looking for a way to search a specific folder for a subfolder containing a certain string," I thought that possibly he was simply looking for a solution to the problem and I wasn't sure if VBA was a requirement for that solution. – Ben McCormack Oct 06 '10 at 02:07
  • 1
    The question is tagged MS-ACCESS and the provided code is VBA. What more do you need to know the applicable platform? – David-W-Fenton Oct 06 '10 at 20:39

2 Answers2

3

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

Garett
  • 16,632
  • 5
  • 55
  • 63
  • The xtremevbtalk link you supplied did the trick for me! Thanks a lot – Icode4food Oct 05 '10 at 18:29
  • 1
    this is great stuff. +1 I wish I'd known about it 15 months ago when I started programming a replacement for the Office FileSearch object (which was removed from Office 2007). For anyone who's interested, I made it available here: http://dfenton.com/DFA/download/Access/FileSearch.html . I never got back to it and fixed many of the pending small details (like making it work on protected directories under Vista/Win7). – David-W-Fenton Oct 05 '10 at 21:37
0

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.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • This doesn't feel like the most elegant solution but it is an interesting idea...the folders are a bit dynamic so I would need to check if they have changed before I trusted it. But it might be doable. – Icode4food Oct 05 '10 at 16:12