How to Find a Directory and Subdirectory in VBA

Updated July 20, 2017

Finding a directory and subdirectories using Visual Basic for Applications (VBA) is not as complicated as you may think. In VBA, you can use the "Dir" function to obtain directory names in a path. Once you have found the directory you're looking for, you can use the directory path to find subdirectories. This process is useful when you need to save a file in a particular directory but don't have the full path for it.

Launch Microsoft Office Excel, click the "Developer" tab and click "Visual Basic." Click the "Insert" menu and click "Module" to insert a new code module.

Type the following to create a new sub procedure:

Private Sub find Directories()

Add the following code to create three variables you will use to find the directory of interest:

Dim start Path As String

Dim myname As String

Dim dirFound As Boolean

Define the drive where you want to search for the directory and get the first directory:

start Path = "C:\"

myname = Dir(start Path, vbDirectory)

Create a while loop, search every directory in "C:\" until the "Temp" directory has been found, and call the "getSubDirectories" sub procedure to get all the subdirectories found in the "Temp" directory:

End Sub

Copy and paste the following code to create the "getSubDirectories" sub procedure and display all the sub directories found in the "Temp" directory using the Immediate window:

Private Sub getSubDirectories(start Path As String)

End Sub

Press "Ctrl" and "G" to display the Immediate window. Click inside "findDirectories" and press "F5" to run the program.

Things You'll Need

  • Microsoft Office Excel
bibliography-icon icon for annotation tool Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.