List folders contained in a folder

[triman]

I want to list all folder names BUT NOT OTHER FILES within a specified folder. For simplicity, output to a sheet.

I would like to know functions down to their roots so I can learn more so please try to use the least amount of already known functions. If not, oh well, as long as I have the ability to do what I need I'll be happy.

Thanks!
Kevin

 

[kieran]

Straight from the excel help file ...

Sub ShowFolderList(folderspec)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.SubFolders
    For Each f1 In fc
        s = s & f1.Name
        s = s & vbCrLf
    Next
    MsgBox s
End Sub

 

[Blade Hunter]

Commented it heaps for you, post back if there is anything you don't understand

Sub FindFolder()
Dim DirName As String
Dim SearchFolder As String
Dim X As Long
SearchFolder = "C:\Temp\"
X = 1
DirName = Dir(SearchFolder, vbDirectory) 'Return files and folders
Do Until DirName = "" 'Loop through all results
    If DirName <> "." And DirName <> ".." Then 'Omit the . and ..
            If GetAttr(SearchFolder & DirName) = 16 Then 'Is it a folder? (16 = Folder, 32 = File)
                Range("A" & X).Formula = DirName 'Post the name to the row defined by X
                X = X + 1 'Add one to X for the next row
            End If
    End If
    DirName = Dir() 'Grab the next filename
Loop
End Sub

 

[triman]

Blade: This is great! Its exactly what I needed. I did see the other method in the help but this helps me understand in a bigger perspective. Thanks!

 

[Blade Hunter]

No worries, happy to help