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!

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

4 thoughts on “The fastest way to find the file in chosen location”

    1. 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.

  1. 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)

    1. 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.

Leave a Reply

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