Loop through files in a folder using VBA?

📝 Tech Blog

🌐 Loop through files in a folder using VBA? 🌐

So, you need to loop through files in a folder using VBA in Excel 2010? We've got you covered! In this post, we'll tackle the common issues that arise when dealing with large numbers of files and provide easy-to-implement solutions. Let's dive in! 💪💻

➡️ The Dilemma: 😰

The provided code works perfectly for folders with fewer than 50 files, but it becomes ridiculously slow for folders with more than 10,000 files. The culprit? The operation to look up file.name is taking an exorbitant amount of time. What can we do to resolve this issue? 🤔

💡 The Solution: 🎉

We've discovered a solution that significantly improves the performance when looping through a large number of files. Follow these steps to optimize your code and get the desired results in no time:

Step 1: Use the Dir function instead of the For Each loop.

Sub LoopThroughFiles()
   Dim fileName As String
   
   fileName = Dir("c:\testfolder\*.*", vbNormal)
   Do While fileName <> ""
       ' Your code logic here
       If InStr(fileName, "test") > 0 Then
           MsgBox "Found"
           Exit Sub
       End If
       ' Move to the next file
       fileName = Dir
   Loop
End Sub

This change alone will significantly speed up your code execution.

Step 2: To retrieve the date when the file was last modified, use the FileDateTime function:

Sub LoopThroughFiles()
   Dim fileName As String
   Dim fileDate As Date
   
   fileName = Dir("c:\testfolder\*.*", vbNormal)
   Do While fileName <> ""
       ' Your code logic here
       fileDate = FileDateTime("c:\testfolder\" & fileName)
       ' Move to the next file
       fileName = Dir
   Loop
End Sub

🚀 Performance Boost: ⚡️

To further enhance the performance, we recommend implementing an additional optimization suggested by another answer. This modification will reduce the execution time to less than 1 second!

Step 3: Disable screen updating and enable manual calculation mode before running the loop:

Sub LoopThroughFiles()
   Dim fileName As String
   Dim fileDate As Date
   
   ' Disable screen updating and enable manual calculation
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   
   ' The rest of your code logic
   
   ' Enable screen updating and restore automatic calculation
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub

🎯 Call-to-Action: 📣

There you have it! Now you know how to efficiently loop through files in a folder using VBA. Implement these changes in your code to achieve lightning-fast file processing. Let us know in the comments below if this solution worked for you or if you have any other tricks up your sleeve. Happy coding! 💡💻

Note: These solutions have been tested and verified on Excel 2010. If you're using a different version, kindly adapt the code accordingly.