動かなくて放置されてた既存マクロを活用するために
指定したフォルダ配下のファイル一覧を取得するVBAマクロを書いてみた。

Sub Sample1()
   Dim buf As String  'ファイル名を格納する変数
   Dim cnt As Long    'カウンターを格納する変数
   Dim Path As String 'ファイルパスを格納する変数
   
   With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = "フォルダを選択してください"
       If .Show = True Then
           Path = .SelectedItems(1) & "\"
       End If
   End With
   
   buf = Dir(Path & "*.xls*")  'エクセルファイルの一覧を取得するため*.xls*を指定
   
   Do While buf <> ""
       cnt = cnt + 1
       Cells(cnt, 1) = Path  '1列目にファイルパスを出力
       Cells(cnt, 2) = buf   '2列目にファイル名を出力
       
       buf = Dir()
   Loop
End Sub

上記マクロをエクセルに登録して実行

以下のダイアログでフォルダを指定する

ファイル名とパスがエクセルシートに一覧で取得されました。

参考URL