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
Thanks so much for this!!!
Hey, my pleasure, Beth!