Tom’s Tutorials For Excel: Listing Subfolders

Tom’s Tutorials For Excel: Listing Subfolders

I previously posted this example to list files kept in a parent folder.

Today’s example shows how you can list the subfolder names belonging to a parent folder.



Here’s the macro that does this:

Sub ListSubfolders()

'Declare and define variables.
Dim SourcePath As String, NextRow As Long
Dim objFSO As Object, objFolder As Object, objSubfolder As Object
SourcePath = "C:\Drivers\"
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Set the folder object associated with your directory path.
'Halt the macro if the directory folder does not exist.
On Error Resume Next
Set objFolder = objFSO.GetFolder(SourcePath)
If Err.Number <> 0 Then
Err.Clear
MsgBox "This parent folder does not exist:" & vbCrLf & _
SourcePath & vbCrLf & _
"Cannot continue.", 16, "No such animal."
Exit Sub
End If

'Define the NextRow variable as row 2.
'Row 1 will contain a header label.
NextRow = 2

'Turn off screen updating.
Application.ScreenUpdating = False

'The subfolder names will be listed in column A.
'Clear column A to start with a clean column.
Columns(1).Clear

'Place a header label in cell A1.
Range("A1").Value = "Subfolders in " & SourcePath

'Loop through the subfolders in the primary folder
'to list its subfolder names.
For Each objSubfolder In objFolder.SubFolders
Cells(NextRow, 1).Value = objSubfolder.Name
NextRow = NextRow + 1
Next objSubfolder

'Release the Objects variables from memory.
Set objFSO = Nothing
Set objFolder = Nothing
Set objSubfolder = Nothing

'For easier readability, autofit column A.
Columns(1).AutoFit

'Turn ScreenUpdating back on.
Application.ScreenUpdating = True

End Sub