Here's some code I posted a while ago. Change the values marked with
'<<< to meet your needs and then run the StartHere procedure.
StartFolderName is the fully qualified name of the folder whose
contents you want to list. Indent indicates whether subdirectories and
file should be indented from their parent folder. ListFiles indicates
whether to list files in addition to folders. R is the range address
at which the listing is to begin. See also
http://www.cpearson.com/Excel/FolderTree.aspx and
http://www.cpearson.com/Excel/FolderTreeView.aspx and
http://www.cpearson.com/Excel/RecursionAndFSO.htm
Sub StartHere()
Dim FSO As Scripting.FileSystemObject
Dim StartFolderName As String
Dim StartFolder As Scripting.Folder
Dim F As Scripting.File
Dim SubF As Scripting.Folder
Dim R As Range
Dim Indent As Boolean
Dim ListFiles As Boolean
Set FSO = New Scripting.FileSystemObject
StartFolderName = "C:\Utilica" ' <<< Start Folder
Indent = True '<<< Indent listing
ListFiles = False '<<< List file names
Set R = Range("A1") '<<< List start cell
Set StartFolder = FSO.GetFolder(StartFolderName)
ListSubFoldersAndFiles FSO, StartFolder, R, Indent, ListFiles
End Sub
Sub ListSubFoldersAndFiles(FSO As Scripting.FileSystemObject, _
FF As Scripting.Folder, _
R As Range, _
Indent As Boolean, ListFiles As Boolean)
Dim SubF As Scripting.Folder
Dim F As Scripting.File
R.Value = FF.Path
For Each SubF In FF.SubFolders
Set R = R(2, 1)
If Indent = True Then
Set R = R(1, 2)
End If
ListSubFoldersAndFiles FSO, SubF, _
R, Indent, ListFiles
If Indent = True Then
Set R = R(1, 0)
End If
Next SubF
If ListFiles = True Then
If Indent = True Then
Set R = R(1, 2)
End If
For Each F In FF.Files
Set R = R(2, 1)
R.Value = F.Name
Next F
If Indent = True Then
Set R = R(1, 0)
End If
End If
End Sub
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com