Excelにフォルダ内のファイル一覧を表示するVBAのサンプルコード

Dim RowNo As Long
Dim No As Long

Sub ボタン1_Click()

    Application.ScreenUpdating = False
    
    RowNo = 5
    No = 1
    
    Call ClearList
    Call FileSearch(Sheets(1).Cells(2, 2))
    
    Application.ScreenUpdating = True
    
End Sub
'一覧初期化
Sub ClearList()
    
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Sheets(1).Rows("5:" & Cells.Rows.Count).Delete
    
End Sub
'ファイル検索
Sub FileSearch(Path As String)

    Dim FSO As Object
    Dim Folder As Variant
    Dim File As Variant
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'サブフォルダ検索
    For Each Folder In FSO.GetFolder(Path).SubFolders
        Call FileSearch(Folder.Path)
    Next Folder
    
    'ファイル処理
    For Each File In FSO.GetFolder(Path).Files
        'No
        Sheets(1).Cells(RowNo, 1).Value = No
        No = No + 1
        
        'ファイル名
        Sheets(1).Cells(RowNo, 2).Value = File.Name
        'フルパス
        Sheets(1).Cells(RowNo, 3).Value = File.Path
        'リンク
        Sheets(1).Hyperlinks.Add Anchor:=Cells(RowNo, 4), Address:=File.Path, TextToDisplay:="リンク"
        
        RowNo = RowNo + 1
        
    Next File
    
End Sub