vba Excel宏根据关键字搜索文件，如果存在则返回true
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18673512/
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
Excel Macro to search for files based on a keyword and return true if present
提问by Kamall
Can someone help me out with a excel VBA macro to search for files in various directories provide in column B, based on keywords given in column A and return "File Present"/"File Not Present" in column C.
有人可以帮助我使用 excel VBA 宏根据 A 列中给出的关键字搜索 B 列中提供的各种目录中的文件，并在 C 列中返回“文件存在”/“文件不存在”。
Example
例子
Keyword | FolderPath | Result
关键字 | 文件夹路径 | 结果
1234 | E:\Documents\ABC
第1234章 E:\文档\ABC
Apple | F:\
苹果 | F：\
File2 | E:\Documents\Test\
文件 2 | E:\文档\测试\
I'm new to excel Macros. Please help me out!
我是 excel 宏的新手。请帮帮我！
Thanks in advance!
提前致谢！
回答by Gary's Student
Give this a try:
试试这个：
Sub IsItThere()
Dim KeyWd As String
Dim Pathh As String, fName As String
Dim N As Long, J As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
For J = 1 To N
KeyWd = Cells(J, 1).Value
Pathh = Cells(J, 2).Value
If Right(Pathh, 1) = "\" Then
Pathh = Mid(Pathh, 1, Len(Pathh) - 1)
End If
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace((Pathh))
For Each strFileName In objFolder.Items
fName = objFolder.GetDetailsOf(strFileName, 0)
If InStr(1, fName, KeyWd) > 0 Then
Cells(J, 3).Value = "File Present"
GoTo NextRecord
End If
Next
Cells(J, 3).Value = "File Not Present"
NextRecord:
Set objFolder = Nothing
Set objShell = Nothing
Next J
End Sub
