用于在文件夹中搜索关键字的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20855542/
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 to search a folder for keyword
提问by JamieB
I'm looking at building a VBA macro that will 'search' a folder for a keyword term 'fuel' and pull back all the information from all returned files and put them into a 'data' sheet.
我正在考虑构建一个 VBA 宏，它将“搜索”一个文件夹中的关键字词“燃料”，并从所有返回的文件中提取所有信息并将它们放入“数据”表中。
For instance, I have a folder week numbers (1 - 52 cross the year so in the new year this will only contain one folder but will build and build as the year goes on) so I search this folder for all .doc files contains the word 'fuel'. You can do this via a windows search just by typing in "fuel" in the search function in the top corner and it will display all filenames and all files contains the words 'fuel' inside of them.
例如，我有一个文件夹周数（跨年为 1 - 52，因此在新的一年中这将只包含一个文件夹，但会随着年份的推移而构建和构建）所以我在此文件夹中搜索所有 .doc 文件，其中包含“燃料”这个词。您可以通过 Windows 搜索完成此操作，只需在顶部角落的搜索功能中输入“fuel”，它将显示所有文件名，并且所有文件都包含其中包含“fuel”字样。
I currently have but this is only searching for a file that has 'fuel' within its name rather than containing insisde of it.
我目前有，但这只是搜索名称中包含“燃料”而不是包含其内部内容的文件。
Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("c:\testfolder\")
While (file <> "")
If InStr(file, "fuel") > 0 Then
MsgBox "found " & file
Exit Sub
End If
file = Dir
Wend
End Sub
I have done some searching around for this, but I just cant get anything to work :(. My VB skills need a refresher I think.
我已经为此进行了一些搜索，但我无法获得任何工作:(。我认为我的 VB 技能需要复习。
Thank you in advanced.
先谢谢了。
JB
JB
回答by JDunkerley
It isn't particularly pretty, but think something like this should work:
它不是特别漂亮，但认为这样的事情应该有效：
Sub loopThroughFiles()
Dim file As String
file = FindFiles("C:\TestFolder", "fuel")
If (file <> "") Then MsgBox file
End Sub
Function FindFiles(ByVal path As String, ByVal target As String) As String
' Run The Sheell Command And Get Output
Dim files As String
Dim lines
files = CreateObject("Wscript.Shell").Exec("FIND """ & target & """ """ & path & "\*.*""").StdOut.ReadAll
lines = Split(files, vbCrLf)
' Look for matching files
Dim curFile As String
Dim line
For Each line In lines
If (Left(line, 11) = "---------- ") Then
curFile = Mid(line, 12)
End If
If (line = target) Then
FindFiles = curFile
Exit Function
End If
Next
FindFiles = ""
End Function
Uses the FIND command line and then reads the output (hence needing to use Wscript.Shell) and returns first match, or empty string if no file is found
使用 FIND 命令行，然后读取输出（因此需要使用 Wscript.Shell）并返回第一个匹配项，如果未找到文件则返回空字符串
Following @BLUEPIXY's command FINDSTR /M the function can be replaced by:
在@BLUEPIXY 的命令 FINDSTR /M 之后，该函数可以替换为：
Function FindFiles(ByVal path As String, ByVal target As String) As String
' Run The Shell Command And Get Output
Dim files As String
files = CreateObject("Wscript.Shell").Exec("FINDSTR /M """ & target & """ """ & path & "\*.*""").StdOut.ReadAll
FindFiles = ""
If (files <> "") Then
Dim idx As Integer
idx = InStr(files, vbCrLf)
FindFiles = Left(files, idx - 1)
End If
End Function
