【VBA】Get folder list in folder

VBA Code

As an example, get folder list in “test” folder.

Option Explicit

Sub sample()
    
    Dim inputFolder As String
    Dim outputWs As Worksheet
    Dim outputColumn As Long
    Dim outputRow As Long
    Dim fso As Object
    Dim folder As Object

    inputFolder = "C:\Users\user\Desktop\test"
    Set outputWs = Worksheets("sample")
    outputColumn = 2
    outputRow = 2
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Repeat for number of folders
    For Each folder In fso.getFolder(inputFolder).SubFolders
        'Output folder name to sheet
        outputWs.Cells(outputRow, outputColumn) = folder.Name
        'Increment output lines
        outputRow = outputRow + 1
    Next
    
    'Automatic adjustment of column widths
    outputWs.Columns(outputColumn).AutoFit
    
    Set fso = Nothing
    
End Sub

Result

You got “folder list in folder“！

FYI No.1

Also, you can get “file list in folder“！

For more information, see the following article:

FYI No.2

For more information about following, see the following official online manual:

●“GetFolder method” of “FileSystemObject”

●“SubFolders property” of “Folder Object”

●Folders collection

●“Name property” of “Folder Object”