The VBA Dir function is crucial to exploring any file system with the use of VBA – whether in Excel or other MS Office applications. In some case true you can resort to a Select File Dialog, however, in other cases you need to be able to easily match files or directories in file systems in VBA. Today, let us learn how to use the Dir function.
VBA Dir Syntax
The Excel Dir function returns the first filename (directory) that matches the path and the provided attributes. To retrieve subsequent filenames (directories) that match the path and attributes call the Dir function again without providing ANY arguments.
The Dir function by default DOES NOT TRAVERSE the entire directory structure provided by the given path. It will match filenames (directories) matching the path and attributes within only the given path. Read on to learn how to use Dir to traverse the whole directory structure.
VBA Dir example
Let’s start with a simple example before we jump into more advanced examples. Let’s see how to explore the C:\Root\ directory:
Dim path As String, currentPath As String path = "C:\Root\" currentPath = Dir(path) Do Until currentPath = vbNullString Debug.Print currentPath currentPath = Dir() Loop
Notice that I highlighted on purpose both calls to the Dir function. The first Dir(path) call will return the first filename within the C:\Root\ directory. Any subsequent Dir() (notice no arguments) calls will return next files under the directory. Also as I have not provided any attributes the Dir function will only match against vbNormal (normal files)!
Assuming the same directory structure as in the previous example let’s change the default vbNormal attribute to vbDirectory. This will allow us to explore not only file matched under the given path but also the directories.
Dim path As String, currentPath As String path = "C:\Root\" currentPath = Dir(path, vbDirectory) Do Until currentPath = vbNullString Debug.Print currentPath currentPath = Dir() Loop
Similarly you can use the other VBA Dir attributes to search for hidden, system files, aliases and volumes. Most attributes include normal files so be aware the the files listed WON’T BE LIMITED to just e.g. hidden files.
The VBA Dir function works great also with wild cards:
- * – multiple character wildcard, including zero-length
- ? – single character wildcard
Some common examples:
Dir("C:\HelloWorld.txt") 'Matches: HelloWorld.txt Dir("C:\Hello*World.txt") 'Matches: HelloWorld.txt, HelloThereWorld.txt, Hello_World.txt etc. Dir("C:\?elloWorld.txt") 'Matches: HelloWorld.txt, MelloWorld.txt, YelloWorld.txt etc.
The VBA Dir function unfortunately by default does not traverse the entire directory, exploring subfolders. In the examples we were limited to just listing the files and folders of the C:\Root directory:
A simple recursive VBA Dir implementation won’t do either as the Dir function is reset whenever it is called globally with any arguments. Fortunately, we can use a native VBA object called the VBA Collection. We will stack all subsequent directories onto our Collection and then explore them recursively. The TraversePath function will do just that:
Sub TraversePath(path As String) Dim currentPath As String, directory As Variant Dim dirCollection As Collection Set dirCollection = New Collection currentPath = Dir(path, vbDirectory) 'Explore current directory Do Until currentPath = vbNullString Debug.Print currentPath If Left(currentPath, 1) <> "." And _ (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then dirCollection.Add currentPath End If currentPath = Dir() Loop 'Explore subsequent directories For Each directory In dirCollection Debug.Print "---SubDirectory: " & directory & "---" TraversePath path & directory & "\" Next directory End Sub
Let’s try it out on our C:\Root directory example:
Sub Test() TraversePath "C:\Root\" End Sub