The fastest way to find the file in chosen location

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.

The fastest way to find the file in chosen location jeremy clarkson

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

Speed 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 Sub

Results

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!

Tomasz Płociński

Author: Tomasz Płociński

Excel VBA enthusiast who is also open for other languages. Mainly working in VBA, some SQL, hungry for more.

Leave a Reply

Your email address will not be published. Required fields are marked *