Loop through files in a folder using VBA?

Question

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

In the loop, I will need:

• the filename, and
• the date at which the file was formatted.

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.

Code that works but is waaaaaay too slow (15 seconds per 100 files):

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

---

Problem solved:

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.

Answer 1

Dir takes wild cards so you could make a big difference adding the filter for test up front and avoiding testing each file

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("c:\testfolder\*test*")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
    Loop
End Sub