Macro to List folder and count sub-folder one level down

[cjouban]

I have searched and cannot find a way of doing this. I need to list the folders and count the subfolders within that folder, but stop there. I don't want to count the sub-folders sub-folder.

An output like this would be great

[TABLE="width: 168"]
<tbody>[TR]
[TD]Folder[/TD]
[TD]Sub Count[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]1005[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]1235[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]1450[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any help

 

[cjouban]

Bump. I'm still struggling with this. Any help would be much appreciated.

 

[John_w]

Try this macro. Change the startFolderPath to the full path of the folder containing the 2018, 2017, etc. folders.

Public Sub Count_Subfolders()
    
    Dim startFolderPath As String
    Dim FSO As Object
    Dim startFolder As Object, subfolder As Object
    Dim r As Long
    
    startFolderPath = "C:\path\to\start\folder"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set startFolder = FSO.GetFolder(startFolderPath)
    
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:B1").Value = Array("Folder", "Subfolder count")
        r = 1
        For Each subfolder In startFolder.SubFolders
            r = r + 1
            .Cells(r, 1).Value = subfolder.Name
            .Cells(r, 2).Value = subfolder.SubFolders.Count
        Next
    End With
        
End Sub

 

[cjouban]

Works Perfect. Thank you.