vba - DIR 无法正常工作

Question

我正在使用 VBA 将数据从 .txt 文件导入到电子表格的表格中，我将其用于进一步的数据透视图。我从中导入文件的网络目录包含约 5500 个文件，并且随着时间的推移，目前将以每年约 2000 个文件的速度增长。表中的条目按日期排序(从最旧到最新)。

我有一个宏，它检查最近条目的日期，然后使用 DIR 搜索网络位置并迭代该目录中的文件。对于每个文件，如果该文件比最新条目更新，我想导入数据并将其添加到表中。如果文件较旧，我希望 DIR 移动到下一个文件。下面是我当前使用的代码。

Sub NewFilesFromNetwork()

Dim myDatabase As Worksheet
Set myDatabase = Sheets("Database")

Dim TotalRows As Long, LastDate As Date

TotalRows = myDatabase.ListObjects("Table1").Range.Rows.Count
LastDate = Cells(TotalRows + 48, 6).Value 'the "+48" here is important because there are 48 hidden rows at the top of the spreadsheet before the table starts

Dim MyFolder As String, MyFile As String

On Error Resume Next
Application.ScreenUpdating = False

MyFolder = "*path to my network location*" 
MyFile = Dir(MyFolder & "*.txt")

Dim t As Integer, k As Integer
t = 0 'counter for calculating total files imported
k = 0 'counter for calculating total files checked

Do While MyFile <> ""
    TxtFile = MyFolder & MyFile
    If FileDateTime(TxtFile) > LastDate Then 
        Open TxtFile For Input As #1 
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
        Close #1
        Call CommonImportCode 'separate sub which picks out information from the .txt file string and adds it to the table as a new entry
        k = k + 1
        t = t + 1
        MyFile = Dir()
    End If

        k = k + 1
        MyFile = Dir()
Loop

Application.ScreenUpdating = True

MsgBox "Number of files searched = " & k & vbNewLine & "Number of files imported = " & t

End Sub

我遇到的问题是:

我可以检查网络位置并看到有 10 个新文件。然而，该宏仅导入其中 5 个，并且似乎仅导入新文件中的所有其他文件。当文件满足 IF 语句的条件时，宏会跳过文件吗？

Answer 1

    k = k + 1
    MyFile = Dir()

该代码是重复的。如果上面的“如果”为真，那么您将跳转一个文件。你的循环应该是:

Do While MyFile <> ""
    TxtFile = MyFolder & MyFile
    If FileDateTime(TxtFile) > LastDate Then 
        Open TxtFile For Input As #1 
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
        Close #1
        Call CommonImportCode 'separate sub which picks out information from the .txt file string and adds it to the table as a new entry
        t = t + 1
    End If
    k = k + 1
    MyFile = Dir()
Loop

或接近的东西。