使用VBA在文件系统中搜索文件路径 [英] Search filesystem for filepath using VBA
问题描述
我正在寻找一种在特定文件夹中搜索包含某个字符串的子文件夹的方法.在下面，我列出了一个函数，该函数在我的头顶上键入，以查看它是否有效.好吧，它确实可以工作，但是当我谈论搜索网络驱动器上的6,000个文件夹时，它的速度还不够快.
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.
我敢肯定，有更好的方法可以做到，但是我似乎无法在Google上挖掘任何东西.
I'm sure that there is a better way to do it but I can't seem to dig anything up on Google.
-
是否有一个允许我利用内置文件系统搜索和索引功能的Windows的对象?
Is there an object that allows me to leverage the windows built in file system searching and indexing capabilities?
作为替代方案，有人可以优化我的代码吗?主要瓶颈是instr的用法.
As an alternative, does someone have a way to optimize my code? The main bottleneck is the usage of instr.
这是代码:
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.我确定有人会建议修改我的文件夹结构，以便我可以以编程方式预测路径，但是由于种种原因，这在我的情况下是不可能的.
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.
推荐答案
您可以使用 FindFirstFileEx 函数，以及 FINDEX_SEARCH_OPS 参数FindExSearchLimitToDirectories，它将搜索范围限制为与指定名称匹配并且还是目录的文件(如果文件系统支持目录过滤).有关从VB/VBA使用这些功能的更多信息，请参见以下内容:
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
