如何(有条件地)比使用FileSystemObject更快地遍历文件 [英] How to traverse files (conditionally) faster than using FileSystemObject

问题描述

我已经使用文件对象编写了一些VBA代码，该文件对象进入文件夹，搜索满足特定条件(文件名中包含"HR"并在指定日期范围内创建)的特定文件(CSV)，并复制/粘贴信息从那个文件变成一个主文件.主文件通常是250多个工作簿的汇编.

I've written some VBA code using file objects to go into a folder, search for particular files (CSV) that meet certain criteria (contain "HR" in filename and created within specified date range), and copy/paste information out of that file into a master file. The master file is typically a compilation of 250+ workbooks.

宏可以正常工作，但是运行大约需要12分钟，这有点多余.我相信它需要花费很长时间才能运行，因为它正在索引其中包含30,000+个文件的文件夹.

The macro works as it should, but it takes about 12 minutes to run, which is a bit excessive. I believe it takes so long to run because it is indexing a folder with 30,000+ files in it.

我已经在下面复制了我的代码的相关行，如果有人知道我可以进行的任何修改都会减少我的宏的持续时间，我将非常感激.我对VBA和编码一般还比较陌生，因此我正在学习这些东西！谢谢！

I've copied the relevant lines of my code below, if anyone is aware of any modifications I could make that would decrease the duration of my macro, I would really appreciate it. I'm relatively new to VBA and coding in general, so I'm learning as I go with these sorts of things! Thanks!

Dim FilePath As String
Dim FileName As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As file
Dim fileDate As Date
Dim firstDate As Date
Dim secondDate As Date

'Defining the user-input variables
Worksheets("Sheet1").Activate
firstDate = Cells(2, "E").Value
secondDate = Cells(3, "E").Value

'FilePath to information, defining file objects
FilePath = "\\SRV-1\process\DUMP\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FilePath)

'Going through Dump folder and finding high resolution files created within the date range
    For Each objFile In objFolder.Files

        'Checking to see if the file contains the string "HR", indicating high resolution.
        If InStr(1, objFile.Name, "HR") Then GoTo Line1 Else GoTo Line3

Line1:
        'Storing the file as a variable and checking its creation date
        FileName = objFile.Name
        OpenFile = FilePath & FileName
        fileDate = FileDateTime(OpenFile)

        'Checking to see if the file was created between the user input master roll start/end dates
        If firstDate < fileDate And secondDate > fileDate Then GoTo Line2 Else GoTo Line3

Line2:
    Do stuff: open dump workbook, copy/pase certain range into main workbook, close dump workbook, next objFile

Line3:
    Next objFile

推荐答案

考虑到HR文件与总文件的比例(250/30,000)，这应该显示出一些改进.

This ought to show some improvement, considering the ratio of HR files to total files (250 / 30,000).

这里的想法是先使用Dir函数来获取包含"HR"子字符串的所有文件名的列表，而仅对这些文件使用FileSystemObject来获取时间戳信息-没有使用该目录中每个 文件上的FSO开销.

The idea here is to use the Dir function first to get a list of all file names that contain the "HR" substring, and only use the FileSystemObject against those files to get the timestamp information -- there's no use incurring the overhead of FSO on every file in that directory.

然后，我们仅处理 符合"HR"标准的文件:

Then, we process only those files which match the "HR" criteria:

Sub usingDir()
Dim folderPath As String
Dim fileName As String
Dim filesToProcess As New Collection
Dim item As Variant
Dim fileDate As Date
Dim firstDate As Date
Dim secondDate As Date

'Defining the user-input variables
firstDate = Cells(2, "E").Value
secondDate = Cells(3, "E").Value
folderPath = "\\SRV-1\process\DUMP\"

' Gets a collection of files matching the "HR" criteria
fileName = Dir(folderPath)
Do While Not fileName = ""
    If InStr(fileName, "HR") > 0 Then
        'Only processing files with "HR"
        filesToProcess.Add (folderPath & fileName)
    End If
    fileName = Dir
Loop

'Now we deal only with the "HR" files:
With CreateObject("Scripting.FileSystemObject")
    For Each item In filesToProcess
        ' Check the date last modified
        fileDate = .GetFile(item).DateLastModified ' modify as needed
        If firstDate < fileDate And secondDate > fileDate Then
            '
            '
            Debug.Print item
            'your code to Do Stuff goes here
            '
            '
            '
        End If
    Next
End With
End Sub

更新:不使用FileSystemObject

这让我很烦，我认为必须有一种方法可以在不依赖FileSystemObject的情况下获取时间戳信息 .有.我们仍将使用Dir遍历文件，但是现在我们将消除对FileSystemObject的任何引用，并替换为一些精美的WinAPI函数调用.在此处中查看Chip Pearson的文章，然后下载

UPDATE: Without Using the FileSystemObject

This was nagging at me, and I figured there must be a way to get the timestamp information without relying on FileSystemObject. There is. We'll still use Dir to traverse the files, but now we'll eliminate any reference to FileSystemObject and replace with some fancy WinAPI function calls. Check out Chip Pearson's article here and download the .bas modules. You'll need the following two files imported to your VBProject:

• modGetSetFileTimes
• modTimeConversionFunctions

然后您可以执行以下操作:

And then you can do something like this:

Option Explicit
Sub withoutFSO()
Dim folderPath As String
Dim FileName As String
Dim filesToProcess As New Collection
Dim item As Variant
Dim fileDate As Date
Dim firstDate As Date
Dim secondDate As Date

'Defining the user-input variables
firstDate = Cells(2, "E").Value
secondDate = Cells(3, "E").Value
folderPath = "\\Your\Path"

' Gets a collection of files matching the "HR" criteria and our Date range
FileName = Dir(folderPath)
Do While Not FileName = ""
    'Only processing files with "HR"
    If InStr(FileName, "HR") > 0 Then
        ' Only process files that meet our date criteria
        fileDate = CDate(modGetSetFileTimes.GetFileDateTime(CStr(item), FileDateLastModified))
        If firstDate < fileDate And secondDate > fileDate Then
            filesToProcess.Add (folderPath & FileName)
        End If
    End If
    FileName = Dir
Loop

'Now we deal only with the matching files:
For Each item In filesToProcess
    Debug.Print item
    Debug.Print fileDate
    'your code to Do Stuff goes here
    '
    '
    '
Next
End Sub

即使是我最初的回答，这也应该是一个改进，并且，如果结合使用更有效的数据检索方式(例如，如果可能，使用ADO代替Workbooks.Open)，那么您应该非常优化.

This should be an improvement even over my original answer, and, if combined with a more efficient manner of retrieving data (i.e., using ADO instead of Workbooks.Open, if possible) then you should be very optimized.

这篇关于如何(有条件地)比使用FileSystemObject更快地遍历文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！