How to loop through folders and files

We often write tools that process files in a given location, i.e. in a given folder. Sometimes it is even whole structure of folders with files. In today’s article You will learn how to loop through folders and files to find the one You look for.

loop through folders and files liam neeson

Loop through files

So, as always, let’s begin with the example situation. Image creating macro, which should check every single file in given location. It is not important what process now. In this case it is only folders and files things valid.

You need to create function which loops inside given folder and checks its names.

First of all set the Microsoft Scripting Runtime reference in the Tools/Reference. This is needed to declare file or folder type variable.

Secondly, You need folder and file name that You will loop for. Those will be variables given into function.

And the last thing, create a loop through files inside of the given folder. That’s all.

Option Explicit
Dim foldName As String

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

You’ve created a function, which looks for file inside the folder. If it finds, it returns the folder path. If not, it returns No file.

Now You may ask “How am I supposed to deliver folder to this function?”.

You can do something like this.

Dim FileSystem As FileSystemObject
Set FileSystem = New FileSystemObject
picpath = findFileInFolder(FileSystem.GetFolder("file path"), _
    "file name")

It creates folder from file path. Or You can do something else.

Loop through folders

Now imagine, that inside given location, there are some other folders, which You need to seek through too.

In this case You need to loop through sub-folders of given location and then loop through files. Just additional loop. Sounds simple.

Personally I was testing this many times and found that I can call the same function to hit the lowest sub-folder. After that, function will go to its second part and loop through files. To make it work correctly, You had to add few additional variables, which are describing the status of search (f_exit) and handling final value of function (foldname).

Option Explicit
Dim f_exit As Boolean
Dim foldName As String

Function findFileInSubfolder(fold As Folder, fname As String)

Dim f_file As File
Dim SubFolder As Folder

f_exit = False

For Each SubFolder In fold.SubFolders
    If f_exit = False Then
        Call findFileInSubfolder(SubFolder, fname)
    Else
        findFileInSubfolder = foldName
        Exit Function
    End If
Next

If f_exit = True Then
    findFileInSubfolder = foldName
    Exit Function
End If

For Each f_file In fold.Files
    If f_file.Name = fname Then
        foldName = fold
        f_exit = True
        findFileInSubfolder = foldName
        Exit Function
    End If
Next

End Function

Give it a try and You’ll see how it is working.

Finally…

Now You know how to loop through folders and files in given location. I think this will be useful for You more than once. Enjoy it as much as it is possible!

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.

2 thoughts on “How to loop through folders and files”

Leave a Reply

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