You should use recursion to handle the subfolder and their subfolders,
etc. Recursion is a technique in which a function calls itself as
need. In the code below, the DoFolder function calls itself for each
subfolder of the input folder. It continues to call itself as deep as
there are subfolers. See
http://www.cpearson.com/excel/RecursionAndFSO.htm for info about
recursion and see
http://www.cpearson.com/excel/FolderTree.aspx for an
add-in that automatically lists folders, subfolders and files.
The code below requires a reference to the Scripting Runtime library.
In VBA, go to the Tools menu, choose References, and then scroll down
to and check "Microsoft Scripting RunTime".
Sub AAA()
Dim FSO As Scripting.FileSystemObject
Dim FF As Scripting.Folder
Dim StartFolder As String
Dim StartCell As Range
Dim Indent As Boolean
Dim ListFiles As Boolean
StartFolder = InputBox("Enter folder path:")
If StartFolder = vbNullString Then
Exit Sub
End If
If Dir(StartFolder, vbDirectory) = vbNullString Then
Exit Sub
End If
On Error Resume Next
Set StartCell = Application.InputBox( _
prompt:="Select start cell.", Type:=8)
If StartCell Is Nothing Then
Exit Sub
End If
On Error GoTo 0
Indent = MsgBox("Indent listing?", vbYesNo) = vbYes
ListFiles = MsgBox("List files?", vbYesNo) = vbYes
Set FSO = New Scripting.FileSystemObject
Set FF = FSO.GetFolder(StartFolder)
DoFolder FF, StartCell, ListFiles, Indent
End Sub
Sub DoFolder(FF As Scripting.Folder, R As Range, ListFiles As Boolean,
Indent As Boolean)
Dim F As Scripting.File
Dim SubF As Scripting.Folder
R.Value = FF.Path
If Indent = True Then
Set R = R(1, 2)
End If
If ListFiles = True Then
For Each F In FF.Files
Set R = R(2, 1)
R.Value = F.Name
Next F
End If
Set R = R(2, 1)
For Each SubF In FF.SubFolders
DoFolder SubF, R, ListFiles, Indent
Next SubF
If Indent Then
Set R = R(1, 0)
End If
End Sub
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
