It's not intuitive, but yes, Dir can do what you're looking to do.
You need to code a recursive routine. Since you can't nest Dir calls, you
need to store the subfolders in a collection (or an array), and then process
each subfolder once you're done with the files of the parent.
Public Sub ListFilesUsingDir( _
StartDir As String, _
FileList As Collection _
)
Dim strFile As String
Dim strFolder As String
Dim strSubfolder As String
Dim varFolder As Variant
Dim colSubfolders As Collection
' Ensure the starting folder is
' correctly formatted
strFolder = QualifyFolderPath(StartDir)
If Len(strFolder) > 0 Then
' Add each of the files in the folder
' to the Collection
strFile = Dir$(strFolder & "*.*")
Do While Len(strFile) > 0
FileList.Add strFolder & strFile
strFile = Dir$
Loop
' Build a list of subfolders in the folder,
' adding each one to a local collection
Set colSubfolders = New Collection
strSubfolder = Dir$(strFolder, vbDirectory)
Do While Len(strSubfolder) > 0
If (strSubfolder <> ".") And _
(strSubfolder <> "..") Then
If (GetAttr(strFolder & strSubfolder) _
And vbDirectory) = vbDirectory Then
strSubfolder = strFolder & strSubfolder
colSubfolders.Add strSubfolder
End If
End If
strSubfolder = Dir$
Loop
' Process each subfolder found above recursively
' Note that you must use a variant when
' enumerating the elements of a collection,
' but our function is expecting a string.
For Each varFolder In colSubfolders
strFolder = varFolder
Call FilesUsingDir(strFolder, FileList)
Next varFolder
End If
End Sub
You'd call this routine using code similar to:
Public Sub dirTest()
Dim colFileList As Collection
Dim intLoop As Integer
Dim strStartDir As String
Dim strMessage As String
strStartDir = "C:\"
Set colFileList = New Collection
Call ListFilesUsingDir( _
strStartDir, colFileList)
If colFileList.Count = 1 Then
strMessage = "There is 1 file under "
Else
strMessage = "There are " & _
colFileList.Count & " files under "
End If
strMessage = strMessage & strStartDir
Debug.Print strMessage
For intLoop = 1 To colFileList.Count
Debug.Print colFileList(intLoop)
Next intLoop
Debug.Print
Set colFileList = Nothing
End Sub
QualifyFolderPath, referred to in ListFilesUsingDir above, is simply a
helper function I use to ensure that the folder name ends in a slash:
Function QualifyFolderPath(PathName As String) _
As String
If Len(PathName) > 0 Then
If Right$(PathName, 1) <> "\" Then
QualifyFolderPath = PathName & "\"
Else
QualifyFolderPath = PathName
End If
Else
QualifyFolderPath = ""
End If
End Function
