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:
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)
Press "Ctrl" and "G" to display the Immediate window. Click inside "findDirectories" and press "F5" to run the program.