Get list of subfolders

[Darren Hill]

How do I create an array containing the list of all subfolders beneath a
specified folder (call it "C:\MyRoot"), and print that array as a list on
an excel spreadsheet?

Darren

 

[Jim Cone]

Darren,

My Excel add-in "List Files" does that.
1. Folders and subFolders in a specified drive or folder can be listed.
or
2. Files in the specified folder, directory or drive can be listed.
Files in sub-folders are listed if requested.
Specific file types or names can be listed. (*.pdf, *.wav, etc)
Each file name, size, date and type go on the list.
Each file name on the list is hyperlinked.

All arranged in columns and rows that can be sorted.
Available - free - upon direct request.
Remove xxx from my email address

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)

 

[Tom Ogilvy]

If you don't need the intermediate array, the example from HELP on DIR shows
the way. Here it is modified:

Sub ListSubs()
Dim MyPath As String, MyName As String
Dim rw As Long

rw = 1
MyPath = "c:\MyRoot\" ' 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
Cells(rw, 1).Value = MyName ' Display entry only if it
rw = rw + 1 ' represents a directory.
End If
End If
MyName = Dir ' Get next entry.
Loop
End Sub

 

[Darren Hill]

Thanks, Tom, that will do perfectly - I don't need the array.

Darren