vba Excel 2007 中的 Application.filesearch w/loop
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17671725/
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
Application.filesearch in Excel 2007 w/loop
提问by Ashencross
I've done several searches and I'm having trouble to find the right code when using the now non-existent .filesearch - I've looked into using both Dir and FileSystemObject but with nothing short of confusion when using a loop after the search... I'm hoping you might be able to help me come to an easier conclusion!
我已经进行了几次搜索，但在使用现在不存在的 .filesearch 时找不到正确的代码 - 我已经研究过使用 Dir 和 FileSystemObject 但在搜索后使用循环时没有任何混淆...我希望你能帮我得出一个更简单的结论！
In short, my current code searches a folder for all excel files, and opens the first, does what it needs to do with it, closes it, and opens the next searched file. Thanks in advance!
简而言之，我当前的代码在一个文件夹中搜索所有 excel 文件，然后打开第一个，执行它需要做的事情，关闭它，然后打开下一个搜索到的文件。提前致谢！
FilePath = "S:\My\File\Path"
FileSpec = ".xls"
Set FS = Application.FileSearch
With FS
.LookIn = FilePath
.Filename = FileSpec
.Execute
End With
For b = 1 To FS.FoundFiles.Count
StrFile = FS.FoundFiles(b)
Set mobjXL = New Excel.Application
With mobjXL
.Visible = False
'REST OF CODE HERE
next b
回答by UberNubIsTrue
This should get you pointed in the right direction:
这应该让你指向正确的方向：
Sub Your_Sub()
Dim FSO as Object
Dim FSO_FOLDER AS Object
Dim FSO_FILE as Object
Dim FILE_PATH as String
Dim FILE_EXT as String
FILE_PATH = "S:\My\File\Path"
FILE_EXT = "xls"
''Create FileSystem Objects
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FSO_FOLDER = FSO.GetFolder(FILE_PATH)
If FSO_FOLDER.Files.Count > 0 Then
''Loop through each File in Folder
For Each FSO_FILE IN FSO_FOLDER.Files
''Test extension
If FSO.GetExtensionName(FSO_FILE.Name) = FILE_EXT Then
''Do your thing here
Else:End if
Next
Else
Msgbox "No Files Found at " & FILE_PATH
End If
Set FSO = Nothing
Set FSO_FOLDER = Nothing
End Sub
回答by Yoan Tournade
For those that want to make old code that rely on Application.FileSearchwork again with as little modification as possible, here a class that you can use as a replacement:
对于那些希望Application.FileSearch通过尽可能少的修改使依赖于工作的旧代码再次工作的人，这里有一个可以用作替代品的类：
https://github.com/MonsieurV/VBA.Application.FileSearch
https://github.com/MonsieurV/VBA.Application.FileSearch
All you have to do is replacing your Set fs = Application.FileSearchby:
您所要做的就是将您Set fs = Application.FileSearch的替换为：
Dim fs As YtoFileSearch
Set fs = New YtoFileSearch
And use it as usual :
并像往常一样使用它：
With fs
.NewSearch
.LookIn = "D:\User\Downloads\"
.fileName = "*.pdf"
If .Execute() > 0 Then
Debug.Print "Found these PDF files:"
For i = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(i)
Next
Else
Debug.Print "Nothing found"
End If
End With
If you don't have legacy code issues, better use directly Scripting.FileSystemObjector Dir()(see UberNubIsTrue answer)
如果您没有遗留代码问题，最好直接使用Scripting.FileSystemObject或Dir()（请参阅UberNubIsTrue 答案）
