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