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.
File Exists
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
Else
FileExists = True
End If
End Function
Sub TestFileExists()
Debug.Print FileExists(“C:Book1.xls”)
End Sub
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.
Sub LoopThroughXLS()
Dim sFName As String
sFName = Dir(“*.xls”)
Do While Len(sFName) > 0
Debug.Print sFName
sFName = Dir
Loop
End Sub
Hi Dick!
Just a personal preference: whenever a result is a boolean, I prefer avoiding the If…Then…End If structure:
Public Function FileExists(sFilePath As String) As Boolean
FileExists = Not (Len(Dir(sFilePath)) = 0 )
End Function.
Hi Dick!
Don’t know how many of your readers are MacXL users. The Windows wildcard characters (*, ?) are legal filename characters for Macs, so they can’t be used with Dir(). Here’s the equivalent cross-platform LoopThroughXLS, using conditional compilation:
Public Sub LoopThroughXLS()
Dim sFName As String
#If Mac Then
sFName = Dir(CurDir, MacID(“XLS8?))
#Else
sFName = Dir(“*.xls”)
#End If
Do While Len(sFName) > 0
Debug.Print sFName
sFName = Dir
Loop
End Sub
Useful function, Dick! The first piece of code is easier to look at quickly and go, yeah, I understand what this does. J.E.M’s would probably run faster if you called the function hundreds or thousands of times.
If you wanted to whittle it down even more, and avoid the len() and not() functions altogether:
Function FileExists(sFilePath As String) As Boolean
FileExists = (Dir(sFilePath) <> “”)
End Function
The thing about this is that you have to look at it and think for a few minutes, what’s really going on here?
Very good!!!
Note also the optional parameter. Like
Function FolderExists(sFolderPath As String) As Boolean
FolderExists = (Dir(sFolderPath, vbDirectory) <> “”)
End Function
Just a quick caution….
If you’re using the DIR command to loop through files, and are calling other routines to process the files, the other routines cannot have their own DIR command used in them.
This resets the location that the DIR command looks at and subsequent calls to DIR in the main routine will fail.
A bit of an amateur on the old Excel and VBA but loving the things it can do! searching the net i wanted to find a way of linking workbooks together.
i have three work books that contain various information and work indepently but on all the 3 workbooks there are 4 columns that share the same information. what i want is some knid of command that send this information to my newly proposed 4th workbook capturing only the information of these 4 columns.
i was thinking of either a VBA command button on the 4th ‘new’ file that when pressed draws the information from the others or maybe an automatic funtion that when lets say the word ‘complete’ is added to each of the individual 3 work books then this information is automatically inputted on the next available empty row on my newly created 4th.
i really need help with the above and since my search on the net brought me to this sight then i guess it’s a great place to start. looking through some of the topics i think i’ll be staying here for a while but as for now i really need some help with the above.
hope you can help
steve
Do you know of a short way to validate the format of a filename is a proper name before it is used on any VBA functions?
Great !!!
Faster and Efficient.
What happens if you know the file name but not the directory?
I would like to have excel look thru a directory and search for a file. For example *01.pdf. If the file is found I want excel to return with the value 1 if not 0. Is this possible?