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.
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!
Awesome tutorials, as usual, my friend.