我只想将最后20个文本文件导入文件夹中的Excel

Question

当前，我从中导入的文件夹有16,000个文件，我只需要最新文件。如果每次尝试运行时，线条没有破坏Excel，那么线条的数量就不会那么糟糕。我正在使用的代码全部导入：

Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range
' Get a FileSystem object
Set fso = New FileSystemObject
' get the directory you want
Set folder = fso.GetFolder("X:TMSTRUCK_OUT")
' set the starting point to write the data to
Set cl = ActiveSheet.Cells(1, 1)
' Loop thru all files in the folder
For Each file In folder.Files
' Open the file
Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
Do While Not FileText.AtEndOfStream
    TextLine = FileText.ReadLine
    ' Parse the line into | delimited pieces
    Items = Split(TextLine, "|")
    ' Put data on one row in active sheet
    For i = 0 To UBound(Items)
        cl.Offset(0, i).Value = Items(i)
    Next
    ' Move to next row
    Set cl = cl.Offset(1, 0)
Loop
' Clean up
FileText.Close
Next file
Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub

Answer 1

查看以下代码 - 我已经评论了我添加的额外代码

Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range
    Dim fileModDate As Date 'PANKAJ - Added the variable
    ' Get a FileSystem object
    Set fso = New FileSystemObject
    ' get the directory you want
    Set folder = fso.GetFolder("C:Userspankaj.jajuDesktop")
    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)
    ' Loop thru all files in the folder
    For Each file In folder.Files
        fileModDate = file.DateLastModified 'PANKAJ - Added this to get the last modified date for each file
        If fileModDate > #1/20/2014 1:00:00 PM# Then 'PANKAJ - Added this to get the desired file based on modified date
            ' Open the file
                Set FileText = file.OpenAsTextStream(ForReading)
                ' Read the file one line at a time
                Do While Not FileText.AtEndOfStream
                    TextLine = FileText.ReadLine
                    ' Parse the line into | delimited pieces
                    Items = Split(TextLine, "|")
                    ' Put data on one row in active sheet
                    For i = 0 To UBound(Items)
                        cl.Offset(0, i).Value = Items(i)
                    Next
                    ' Move to next row
                    Set cl = cl.Offset(1, 0)
                   ' Clean up
                    FileText.Close
                Loop
        End If
    Next
    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub