How to Find a Directory and Subdirectory in VBA

Written by jaime avelar | 13/05/2017
How to Find a Directory and Subdirectory in VBA
Create a sub procedure in Excel using VBA. (Hemera Technologies/Photos.com/Getty Images)

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 need

  • Microsoft Office Excel

Show MoreHide

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.