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

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



"Darren Hill" wrote in
message news:opsm8er2qced89cl@omega...
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

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

--
Regards,
Tom Ogilvy


"Darren Hill" wrote in message
news:opsm8er2qced89cl@omega...
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

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

Darren

On Sun, 6 Mar 2005 15:25:45 -0500, Tom Ogilvy wrote:

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