【VBA】Get file list in folder

VBA Code

As an example, get file 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 file 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 files
    For Each file In fso.GetFolder(inputFolder).Files
        'Output file name to sheet
        outputWs.Cells(outputRow, outputColumn) = file.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 “file list in folder“！

FYI No.1

Also, you can get “folder 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”

●“Files property” of “Folder Object”

●Files collection

●“Name property” of “File Object”