使用 VBA 遍历文件夹中的文件? [英] Loop through files in a folder using VBA?

问题描述

我想使用 遍历目录的文件vba 在 Excel 2010 中.

I would like to loop through the files of a directory using vba in Excel 2010.

在循环中，我需要:

• 文件名，和
• 文件被格式化的日期.

我编写了以下代码，如果文件夹中的文件不超过 50 个，则它可以正常工作，否则它会非常慢(我需要它处理具有 >10000 个文件的文件夹).这段代码唯一的问题是查找file.name 的操作非常耗时.

I have coded the following which works fine if the folder has no more then 50 files, otherwise it is ridiculously slow (I need it to work with folders with >10000 files). The sole problem of this code is that the operation to look up file.name takes extremely much time.

代码有效但太慢(每 100 个文件 15 秒):

Sub LoopThroughFiles()
   Dim MyObj As Object, MySource As Object, file As Variant
   Set MySource = MyObj.GetFolder("c:	estfolder")
   For Each file In MySource.Files
      If InStr(file.name, "test") > 0 Then
         MsgBox "found"
         Exit Sub
      End If
   Next file
End Sub

<小时>

问题已解决:

1. 我的问题已通过以下解决方案解决，使用 Dir 以特定方式(15000 个文件为 20 秒)并使用命令 FileDateTime 检查时间戳.
2. 考虑到另一个答案从 20 秒以下减少到不到 1 秒.

1. My problem has been solved by the solution below using Dir in a particular way (20 seconds for 15000 files) and for checking the time stamp using the command FileDateTime.
2. Taking into account another answer from below the 20 seconds are reduced to less than 1 second.

推荐答案

这是我对函数的解释:

'#######################################################################
'# 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

这篇关于使用 VBA 遍历文件夹中的文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！