使用 Excel VBA 列出特定模式的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/555776/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List files of certain pattern using Excel VBA
提问by Manoj
How to list all the files which match a certain pattern inside a user specified directory? This should work recursively inside the sub folders of the selected directory. I also need a convenient way(like tree control) of listing them.
如何列出用户指定目录中与特定模式匹配的所有文件？这应该在所选目录的子文件夹中递归地工作。我还需要一种方便的方法（如树控件）来列出它们。
回答by user2780436
It appears that a couple answers talk about recursion, and one about regex. Here's some code that puts the two topics together. I grabbed the code from http://vba-tutorial.com
似乎有几个答案谈论递归，一个关于正则表达式。这是将两个主题放在一起的一些代码。我从http://vba-tutorial.com 获取了代码
Sub FindPatternMatchedFiles()
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.pattern = ".*xlsx"
objRegExp.IgnoreCase = True
Dim colFiles As Collection
Set colFiles = New Collection
RecursiveFileSearch "C:\Path\To\Your\Directory", objRegExp, colFiles, objFSO
For Each f In colFiles
Debug.Print (f)
'Insert code here to do something with the matched files
Next
'Garbage Collection
Set objFSO = Nothing
Set objRegExp = Nothing
End Sub
Sub RecursiveFileSearch(ByVal targetFolder As String, ByRef objRegExp As Object, _
ByRef matchedFiles As Collection, ByRef objFSO As Object)
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolders As Object
'Get the folder object associated with the target directory
Set objFolder = objFSO.GetFolder(targetFolder)
'Loop through the files current folder
For Each objFile In objFolder.files
If objRegExp.test(objFile) Then
matchedFiles.Add (objFile)
End If
Next
'Loop through the each of the sub folders recursively
Set objSubFolders = objFolder.Subfolders
For Each objSubfolder In objSubFolders
RecursiveFileSearch objSubfolder, objRegExp, matchedFiles, objFSO
Next
'Garbage Collection
Set objFolder = Nothing
Set objFile = Nothing
Set objSubFolders = Nothing
End Sub
回答by Adam Ralph
As a general pointer, take a look at Application.FileSearch, recursive functions, Userforms and the 'Microsoft TreeView Control'.
作为一般指针，请查看 Application.FileSearch、递归函数、用户窗体和“Microsoft TreeView 控件”。
FileSearch can be used to find files within a folder matching a pattern, a recursive function can call itself until all paths have been exhausted, a UserForm can host controls for displaying your data and the TreeView control can display your file system.
FileSearch 可用于在与模式匹配的文件夹中查找文件，递归函数可以调用自身直到所有路径都用完，用户窗体可以托管用于显示您的数据的控件，而 TreeView 控件可以显示您的文件系统。
Bear in mind that there are pre-built functions/controls which can be used for displaying file systems, e.g. Application.GetOpenFileName, Application.GetSaveAsFileName, Microsoft WebBrowser (given a 'file://...' URL).
请记住，有可用于显示文件系统的预构建函数/控件，例如 Application.GetOpenFileName、Application.GetSaveAsFileName、Microsoft WebBrowser（给定一个“file://...”URL）。
回答by Toby Allen
回答by Joe Mako
Not exactly what you asked for, but I thought I would post this here as it is related.
不完全是你要求的，但我想我会在这里发布它，因为它是相关的。
This is modified from the code found at http://www.cpearson.com/excel/FOLDERTREEVIEW.ASPX
这是从http://www.cpearson.com/excel/FOLDERTREEVIEW.ASPX 上的代码修改而来的
This requires the reference Microsoft Scripting Runtime.
这需要参考Microsoft Scripting Runtime。
Sub ListFilePaths()
Dim Path As String
Dim Files As Long
Path = "C:\Folder"
Files = GetFilePaths(Path, "A", 1)
MsgBox "Found " & Files - 1 & " Files"
End Sub
Function GetFilePaths(Path As String, Column As String, StartRow As Long) As Long
Dim Folder As Scripting.Folder
Dim SubFolder As Scripting.Folder
Dim File As Scripting.File
Dim FSO As Scripting.FileSystemObject
Dim CurrentRow As Long
Set FSO = New Scripting.FileSystemObject
Set Folder = FSO.GetFolder(folderpath:=Path)
CurrentRow = StartRow
For Each File In Folder.Files
Range(Column & CurrentRow).Value = File.Path
CurrentRow = CurrentRow + 1
Next File
For Each SubFolder In Folder.SubFolders
CurrentRow = GetFilePaths(SubFolder.Path, Column, CurrentRow)
Next SubFolder
GetFilePaths = CurrentRow
Set Folder = Nothing
Set FSO = Nothing
End Function
回答by TerrorAustralis
I see that the people above me have already answered how to recurse through the file tree, This might interest you in searching for patterns in the file/file name. It is a Function for VBA that will allow regular expressions to be used.
我看到我上面的人已经回答了如何通过文件树递归，这可能会让您感兴趣在文件/文件名中搜索模式。它是一个用于 VBA 的函数，允许使用正则表达式。
Private Function RegularExpression(SearchString As String, Pattern As String) As String
Dim RE As Object, REMatches As Object
'Create the regex object'
Set RE = CreateObject("vbscript.regexp")
With RE
.MultiLine = False
.Global = False
.IgnoreCase = True
'set the search pattern using parameter Pattern'
.Pattern = Pattern
End With
'Search for the pattern'
Set REMatches = RE.Execute(SearchString)
If REMatches.Count > 0 Then
'return the first match'
RegularExpression = REMatches(0)
Else
'nothing found, return empty string'
RegularExpression = ""
End If
End Function
You can use this to search the file names for patterns. I suggest regular expressions homefor more information on how to use Regular expressions
