DIR Not Functioning Correctly

Question

I am using VBA to import data from .txt files into a table of my spreadsheet which I am using for further pivot charts. The network directory that I am importing the files from contains ~5500 files and will grow over time at about 2000 files per year currently. The entries in the table are sorted by date (oldest to newest).

I have a macro which checks the date of the most recent entry, then uses DIR to search the network location and iterate through the files in that directory. For each file, if the file is newer than the most recent entry, I want to import the data and add it to the table. If the file is older, I want DIR to move to the next file. Below is the code I am currently using.

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

The issue I am having is this:

I can check the network location and see that there are 10 new files. However, the macro only imports 5 of them, and seems to be importing only every other file of the new files. Is there a reason the macro is skipping files when they meet the conditions of the IF statement?

Answer 1

    k = k + 1
    MyFile = Dir()

That code is duplicated. If your "If" just above is true, you are jumping one file. Your loop should be :

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

or something approaching.