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
Loop
End Sub
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
Loop
End Sub
Stephen Bullen:
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.
Regards
Stephen Bullen
20 April 2005, 1:27 amTom Ogilvy:
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.
Loop
--
20 April 2005, 9:49 pmRegards,
Tom Ogilvy
Tom Ogilvy:
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.
--
20 April 2005, 9:53 pmRegards,
Tom Ogilvy
Dick Kusleika:
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.
21 April 2005, 1:00 amNimar:
So how would you loop whrough a series of subfolders within a specified dierectory?
10 January 2006, 4:30 amPete:
This routing will fail when the string that represents the path & file name exceeds 255 characters
24 April 2008, 3:14 amPatrick O'Beirne:
And, Excel cannot open a path+file name longer than 218 characters.
25 April 2008, 2:28 am