Here is a function that might give you the idea, copy into a module, fix any line wrapping, change the path to your path and run the sub

Option Private Modul
Option Explici
Option Base

Private Sub Return_File_List(

Dim arrFileList() As Varian
Dim strPath As Strin
Dim strType As Strin
Dim varItem As Varian
Dim strMsg As Strin

strPath = "C:\Test\" '<Modify this pat
strType = "*.xls" '<Modify the file typ

arrFileList = FileList(strPath, strType, False

For Each varItem In arrFileLis
strMsg = strMsg & varItem & vbC
Nex
MsgBox strMs

End Su

Function FileList(argPath As String, argType As String, argSearchSubFolders As Boolean

Dim ofsSearch As FileSearc
Dim ofsFound As FoundFile
Dim lngX As Lon
Dim intSlash As Intege
Dim strFileName As Strin
Dim arrFileList() As Varian

Set ofsSearch = Application.FileSearc

With ofsSearc
.NewSearc
.SearchSubFolders = argSearchSubFolder
.Filename = argTyp
.LookIn = argPat
.Execute msoSortByFileNam
End Wit
Set ofsFound = ofsSearch.FoundFile

For lngX = 1 To ofsFound.Coun
intSlash = InStrRev(ofsFound(lngX), "\", -1
strFileName = Right(ofsFound(lngX), Len(ofsFound(lngX)) - intSlash
ReDim Preserve arrFileList(lngX
arrFileList(lngX) = strFileNam
Next lng
FileList = arrFileLis

End Function