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.
- Skill level:
- Moderately Easy
Other People Are Reading
Things you need
- Microsoft Office Excel
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:
Do While dirFound = False If myname <> "." And myname <> ".." Then If (GetAttr(start Path & myname) And vbDirectory) = vbDirectory Then If myname = "Temp" Then dirFound = True Call getSubDirectories(start Path & myname & "\") End If End If End If If (dirFound = False) Then myname = Dir End If Loop
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)
Dim myname As String myname = Dir(start Path, vbDirectory) Do While myname <> "" If myname <> "." And myname <> ".." Then If (GetAttr(start Path & myname) And vbDirectory) = vbDirectory Then Debug.Print myname End If End If myname = Dir Loop
Press "Ctrl" and "G" to display the Immediate window. Click inside "findDirectories" and press "F5" to run the program.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for