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:
- 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 commandFileDateTime
. - Taking into account another answer from below the 20 seconds are reduced to less than 1 second.