在循环中,我需要:
我已经编写了以下代码,如果文件夹不超过 50 个文件,它可以正常工作,否则速度会非常慢(我需要它来处理具有 >10000 个文件的文件夹)。这段代码的唯一问题是查找操作file.name需要非常多的时间。
file.name
有效但 waaaaaay 太慢的代码(每 100 个文件 15 秒):
Sub LoopThroughFiles() Dim MyObj As Object, MySource As Object, file As Variant Set MySource = MyObj.GetFolder("c:\testfolder\") For Each file In MySource.Files If InStr(file.name, "test") > 0 Then MsgBox "found" Exit Sub End If Next file End Sub
问题解决了:
Dir
FileDateTime
这是我作为函数的解释:
'####################################################################### '# LoopThroughFiles '# Function to Loop through files in current directory and return filenames '# Usage: LoopThroughFiles ActiveWorkbook.Path, "txt" 'inputDirectoryToScanForFile '# https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba '####################################################################### Function LoopThroughFiles(inputDirectoryToScanForFile, filenameCriteria) As String Dim StrFile As String 'Debug.Print "in LoopThroughFiles. inputDirectoryToScanForFile: ", inputDirectoryToScanForFile StrFile = Dir(inputDirectoryToScanForFile & "\*" & filenameCriteria) Do While Len(StrFile) > 0 Debug.Print StrFile StrFile = Dir Loop End Function