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
- My problem has been solved by the solution below using
Dirin a particular way (20 seconds for 15000 files) and for checking the time stamp using the command
- Taking into account another answer from below the 20 seconds are reduced to less than 1 second.