【VBA】Get file list in folder

You can get  “file list in folder“!

PR

VBA Code

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

“test” folder
“test” folder
There are 5 files in the folder.
There are 5 files in the 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

Set “input folder”, “output sheet”, “output column”, “output row”(Line 12~15).

PR

Result

You got “file list in folder“!

Result
Result
PR

FYI No.1

Also, you can get “folder list in folder“!

For more information, see the following article:

PR

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”

タイトルとURLをコピーしました