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
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
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
Regards,
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.
Regards,
Tom Ogilvy
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.
So how would you loop whrough a series of subfolders within a specified dierectory?
This routing will fail when the string that represents the path & file name exceeds 255 characters
And, Excel cannot open a path+file name longer than 218 characters.