在多个子文件夹中搜索文件的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20687810/
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
VBA macro that search for file in multiple subfolders
提问by trenccan
I have macro, if I put in cell E1 name of the file, macro search trough C:\Users\Marek\Desktop\Makro\ directory, find it and put the needed values in specific cells of my original file with macro.
我有宏，如果我输入文件的单元格 E1 名称，宏搜索槽 C:\Users\Marek\Desktop\Makro\ 目录，找到它并使用宏将所需的值放在原始文件的特定单元格中。
Is it possible to make this work without specific folder location? I need something that can search trough C:\Users\Marek\Desktop\Makro\ with many subfolders in it.
是否可以在没有特定文件夹位置的情况下进行这项工作？我需要一些可以在 C:\Users\Marek\Desktop\Makro\ 中搜索的东西，其中有许多子文件夹。
My code:
我的代码：
Sub Zila1()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
Dim YrMth As String
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Sheets("Sheet1").Range("E1").Text
If FName = False Then
'do nothing
Else
GetData "C:\Users\Marek\Desktop\Makro\" & FName & ".xls", "Vystupna_kontrola", _
"A16:A17", Sheets("Sheet1").Range("B2:B3"), True, False
GetData "C:\Users\Marek\Desktop\Makro\" & FName & ".xls", "Vystupna_kontrola", _
"AE23:AE24", Sheets("Sheet1").Range("B3:B4"), True, False
GetData "C:\Users\Marek\Desktop\Makro\" & FName & ".xls", "Vystupna_kontrola", _
"AE26:AE27", Sheets("Sheet1").Range("B4:B5"), True, False
GetData "C:\Users\Marek\Desktop\Makro\" & FName & ".xls", "Vystupna_kontrola", _
"AQ59:AQ60", Sheets("Sheet1").Range("B5:B6"), True, False
GetData "C:\Users\Marek\Desktop\Makro\" & FName & ".xls", "Vystupna_kontrola", _
"AR65:AR66", Sheets("Sheet1").Range("B6:B7"), True, False
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub
回答by Tete1805
Just for fun, here's a sample with a recursive function which (I hope) should be a bit simpler to understand and to use with your code:
只是为了好玩，这里有一个带有递归函数的示例，（我希望）它应该更容易理解并与您的代码一起使用：
Function Recurse(sPath As String) As String
Dim FSO As New FileSystemObject
Dim myFolder As Folder
Dim mySubFolder As Folder
Set myFolder = FSO.GetFolder(sPath)
For Each mySubFolder In myFolder.SubFolders
Call TestSub(mySubFolder.Path)
Recurse = Recurse(mySubFolder.Path)
Next
End Function
Sub TestR()
Call Recurse("D:\Projets\")
End Sub
Sub TestSub(ByVal s As String)
Debug.Print s
End Sub
Edit: Here's how you can implement this code in your workbook to achieve your objective.
编辑：这是您如何在工作簿中实现此代码以实现目标的方法。
Sub TestSub(ByVal s As String)
Dim FSO As New FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Set myFolder = FSO.GetFolder(s)
For Each myFile In myFolder.Files
If myFile.Name = Range("E1").Value Then
Debug.Print myFile.Name 'Or do whatever you want with the file
End If
Next
End Sub
Here, I just debug the name of the found file, the rest is up to you. ;)
在这里，我只是调试找到的文件的名称，其余的由您决定。;)
Of course, some would say it's a bit clumsy to call twice the FileSystemObject so you could simply write your code like this (depends on wether you want to compartmentalize or not):
当然，有人会说两次调用 FileSystemObject 有点笨拙，因此您可以简单地编写这样的代码（取决于您是否要进行分区）：
Function Recurse(sPath As String) As String
Dim FSO As New FileSystemObject
Dim myFolder As Folder
Dim mySubFolder As Folder
Dim myFile As File
Set myFolder = FSO.GetFolder(sPath)
For Each mySubFolder In myFolder.SubFolders
For Each myFile In mySubFolder.Files
If myFile.Name = Range("E1").Value Then
Debug.Print myFile.Name & " in " & myFile.Path 'Or do whatever you want with the file
Exit For
End If
Next
Recurse = Recurse(mySubFolder.Path)
Next
End Function
Sub TestR()
Call Recurse("D:\Projets\")
End Sub
回答by Tim Williams
This sub will populate a Collection with all files matching the filename or pattern you pass in.
此子将使用与您传入的文件名或模式匹配的所有文件填充集合。
Sub GetFiles(StartFolder As String, Pattern As String, _
DoSubfolders As Boolean, ByRef colFiles As Collection)
Dim f As String, sf As String, subF As New Collection, s
If Right(StartFolder, 1) <> "\" Then StartFolder = StartFolder & "\"
f = Dir(StartFolder & Pattern)
Do While Len(f) > 0
colFiles.Add StartFolder & f
f = Dir()
Loop
sf = Dir(StartFolder, vbDirectory)
Do While Len(sf) > 0
If sf <> "." And sf <> ".." Then
If (GetAttr(StartFolder & sf) And vbDirectory) <> 0 Then
subF.Add StartFolder & sf
End If
End If
sf = Dir()
Loop
For Each s In subF
GetFiles CStr(s), Pattern, True, colFiles
Next s
End Sub
Usage:
用法：
Dim colFiles As New Collection
GetFiles "C:\Users\Marek\Desktop\Makro\", FName & ".xls", True, colFiles
If colFiles.Count > 0 Then
'work with found files
End If
回答by Tete1805
If this helps, you can also use FileSystemObject to retrieve all subfolders of a folder. You need to check the reference "Microsot Scripting Runtime" to get Intellisense and use the "new" keyword.
如果这有帮助，您还可以使用 FileSystemObject 检索文件夹的所有子文件夹。您需要查看参考“Microsot Scripting Runtime”以获取 Intellisense 并使用“new”关键字。
Sub GetSubFolders()
Dim fso As New FileSystemObject
Dim f As Folder, sf As Folder
Set f = fso.GetFolder("D:\Proj\")
For Each sf In f.SubFolders
'Code inside
Next
End Sub
回答by garthhh
I actually just found this today for something I'm working on. This will return file paths for all files in a folder and its subfolders.
实际上，我今天刚刚为我正在做的事情找到了这个。这将返回文件夹及其子文件夹中所有文件的文件路径。
Dim colFiles As New Collection
RecursiveDir colFiles, "C:\Users\Marek\Desktop\Makro\", "*.*", True
Dim vFile As Variant
For Each vFile In colFiles
'file operation here or store file name/path in a string array for use later in the script
filepath(n) = vFile
filename = fso.GetFileName(vFile) 'If you want the filename without full path
n=n+1
Next vFile
'These two functions are required
Public Function RecursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call RecursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function
This is adapted from a post by Ammara Digital Image Solutions.(http://www.ammara.com/access_image_faq/recursive_folder_search.html).
这是改编自 Ammara Digital Image Solutions 的一篇文章。（http://www.ammara.com/access_image_faq/recursive_folder_search.html）。
