How to Find a Directory and Subdirectory in VBA

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
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.

Skill level:
Moderately Easy

Other People Are Reading

Things you need

  • Microsoft Office Excel

Show MoreHide

Instructions

  1. 1

    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.

  2. 2

    Type the following to create a new sub procedure:

    Private Sub find Directories()

  3. 3

    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

  4. 4

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

    start Path = "C:\"

    myname = Dir(start Path, vbDirectory)

  5. 5

    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
    

    End Sub

  6. 6

    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
    

    End Sub

  7. 7

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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