Loop through files in a folder using VBA?
excel, vba
Solution
Here's my interpretation as a Function Instead:
'#######################################################################
'# LoopThroughFiles
'# Function to Loop through files in current directory and return filenames
'# Usage: LoopThroughFiles ActiveWorkbook.Path, "txt" 'inputDirectoryToScanForFile
'# https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba
'#######################################################################
Function LoopThroughFiles(inputDirectoryToScanForFile, filenameCriteria) As String
Dim StrFile As String
'Debug.Print "in LoopThroughFiles. inputDirectoryToScanForFile: ", inputDirectoryToScanForFile
StrFile = Dir(inputDirectoryToScanForFile & "\*" & filenameCriteria)
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Function
Problem
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 command `FileDateTime`. - Taking into account another answer from below the 20 seconds are reduced to less than 1 second.