The Dir function is very versatile. Its two most common uses are to determine if a file exists and to loop through the files in a folder.
The Dir function can accept a string argument that represents a file name. If the file exists, Dir returns the file name as a string. Just the file name, not the path. If the file is not found, a zero-length string is returned. If you just supply a file name, and no path, the current directory is used.
Below is an example of how you might write a function to test whether a file exists. Below that is an example of how the function is called from a Sub.
Function FileExists(sFilePath As String) As Boolean
If Len(Dir(sFilePath)) = 0 Then
FileExists = False
FileExists = True
Looping Through Files
There are two aspects to Dir that make it easy to loop through files in a directory. First, the argument can contain wildcard characters (* and ?) so you can find similar files. Second, calling Dir without an argument finds the next file that matches the argument. Note that you must supply an argument the first time you call Dir.
This example loops through all the xls files in the current directory. It benignly prints their names to the Immediate Window, but you can modify it to open the files (with a Workbooks.Open call) or do just about anything you want.
Dim sFName As String
sFName = Dir(“*.xls”)
Do While Len(sFName) > 0
sFName = Dir