Using Dir With Only Folders

The Dir function can be used to check for the existence of a file, strip out the path, or loop through all the files in a directory. The Attributes argument

Dir(PathName, Attributes)

is used to limit what kinds of file Dir finds. The default, vbNormal, finds files with no attributes. The other arguments find files with certain attributes AND no attributes. That means that you can find directories by using

Dir(“C:”, vbDirectory)

but you’ll also find all the files. To limit the search to just directories (or folders, if you prefer), use the GetAttr function. You loop through both wanted and unwanted files and weed out the ones you don’t want.

Sub ListDirectories()
    Dim sDirName As String
    Dim sPath As String
    sPath = “C:”
    sDirName = Dir(sPath, vbDirectory)
    Do Until Len(sDirName) = 0
        ‘only list folders
       If GetAttr(sPath & sDirName) = vbDirectory Then
            Debug.Print sDirName
        End If
        sDirName = Dir ‘get next file
End Sub
Posted in Uncategorized

7 thoughts on “Using Dir With Only Folders

  1. Hi Dick,

    The GetAttr result enumeration is bitwise, so checking for it to be equal to vbDirectory will miss any folders that have other attributes set (such as read-only or hidden). A better test would be to do it bitwise:

    If GetAttr(sPath & sDirName) And vbDirectory Then

    And also note that this returns the special directories of ‘.’ and ‘..’, which will typically need filtering out.


    Stephen Bullen

  2. Doesn’t the VBA help example for DIR do all that Stephen Suggested??

    ‘ Display the names in C: that represent directories.
    MyPath = “c:”‘ Set the path.
    MyName = Dir(MyPath, vbDirectory)’ Retrieve the first entry.
    Do While MyName “”‘ Start the loop.
    ‘ Ignore the current directory and the encompassing directory.
    If MyName “.” And MyName “..” Then
    ‘ Use bitwise comparison to make sure MyName is a directory.

    If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
    Debug.Print MyName’ Display entry only if it
    End If’ it represents a directory.
    End If
    MyName = Dir’ Get next entry.

    Tom Ogilvy

  3. Guess it requires some trick to post code here. It screws up the quotes and removes not equal to symbols, but the code is in the help, so it can be viewed there.

    Tom Ogilvy

  4. Quite right, Tom. I would have never thought something like this would be in help, but there it is. I guess I should be expecting a letter from MS’s legal department.

  5. This routing will fail when the string that represents the path & file name exceeds 255 characters

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.