I have an add-in that will do exactly that, and more. Seehttp://
www.cpearson.com/excel/FolderTree.aspxfor details. If you to
just have code, the following code will create a list. Copy this code,
then in VBA go to the Tools menu, choose References, and scroll down
to and check "Microsoft Scripting Runtime". Change the lines of code
marked with <<< to your desired needs. StartFolderName is the name of
the folder from which to start the list. Indent indicates whether to
indent the listing in a tree-like structure. ListFiles indicates
whether to list file names in addition to folder names. R is the cell
in which the listing is to begin.
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, LLCwww.cpearson.com
- Show quoted text -
