Last time I showed You how to loop through folders and files. But was that the fastest way? In today’s article I’m going to compare the speed of loops through files methods and get the fastest way to find the file.
Background story, as always
As I told You in the beginning of this article I already showed You method to loop through folders and files to find the one You search for.
Option Explicit
Function findFileInFolder(fold As Folder, fname As String)
Dim f_file As File
For Each f_file In fold.Files
If f_file.Name = fname Then
findFileInFolder = fold
Exit Function
End If
Next
findFileInFolder = "No file"
End FunctionIt works and I was using this for a long time in my tools. But one day I found out, that in case of big amount of files in folder, it is not the fastest method.
I decided to ask uncle Google about this.
The solution was at fingertips
As always, my old friend StackOverflow helped me out with that. There was not the only solution for faster method, but one was and still is my favorite. Of course I had to modify it for my needs, so here it is.
Option Explicit
Function findFileInFolder(fold As Folder, fname As String)
Dim SubFolder As Folder
Dim StrFile As String
StrFile = Dir(fold & "\")
Do While Len(StrFile) > 0
If StrFile = fname Then
findFileInFolder = fold
Exit Function
End If
StrFile = Dir
Loop
findFileInFolder = "No file"
End FunctionSpeed comparison
I made a little test to prove, that method I found out is faster for real. So I prepared a folder with 18,453 files and want to find only 1, the last one.
Sub speed_comp()
Dim start_time As Double
Dim fpath As String
Dim FileSystem As FileSystemObject
fpath = "folder_path"
start_time = Timer
Set FileSystem = New FileSystemObject
Call findFileInFolder(FileSystem.GetFolder(fpath), "file_name")
Debug.Print Format(Timer - start_time, "0.000")
End SubResults
To make this comparison I had to format the result to two decimal places.
Yes, two decimal places. Why?
Second method was so fast, that I needed that many to see the time difference between start and finish.
So, what were the results?
The first method took a full 7 seconds to find that file.
The second method only took 0.04 seconds.
YES, 0.04 seconds! Just a blink of an eye!
Summary
When I implemented second method into code, the tool started to process unbelievable faster. In case of tons of files in folder like in my example – from few hours into 15 minutes.
Now You should know what is the fastest way to find the file in folder. I’m glad I could share that amazing method with You. Take a handful of this way benefits!
An excellent method for single folder search, how will you include subfolders?
Try to create separate function to loop through the subfolders and just call it whenever code find any. It will call function in function in function etc as many times as subfolders.
Why are you looping through every file when you can use pattern matching?? Both methods used are overkill and very inefficient.
I have code that can look for files in folder with 80.000+ pdfs and easily find the NEWEST matching file in no time because I match file as pattern. No reason to have Dir() give you EVERY file:
strItem = Dir(strFolder & strPattern)
Correct. But this is beauty of the coding – there is no the only solution for the problem.
This article can be useful for example if You don’t know the whole name of the file You looking for, Dir will not help in such case.