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 Function
It 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 Function
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 Sub
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!
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!
4 thoughts on “The fastest way to find the file in chosen location”
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.