Function CreateFileList(FileFilter As String, _
    IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching 
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
    CreateFileList = ""
    Erase FileList
    If FileFilter = "" Then FileFilter = "*.*" ' all files
    With Application.FileSearch
        .NewSearch
        .LookIn = CurDir
        .FileName = FileFilter
        .SearchSubFolders = IncludeSubFolder
        .FileType = msoFileTypeAllFiles
        If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
        ReDim FileList(.FoundFiles.Count)
        For FileCount = 1 To .FoundFiles.Count
            FileList(FileCount) = .FoundFiles(FileCount)
        Next FileCount
        .FileType = msoFileTypeExcelWorkbooks ' reset filetypes
    End With
    CreateFileList = FileList
    Erase FileList
End Function

Sub TestCreateFileList()
Dim FileNamesList As Variant, i As Integer
    'ChDir "C:\My Documents" 
    ' activate the desired startfolder for the filesearch
    FileNamesList = CreateFileList("*.*", False) 
    ' performs the filesearch, includes any subfolders
    ' present the result
    Range("A:A").ClearContents
    For i = 1 To UBound(FileNamesList)
        Cells(i + 1, 1).Formula = FileNamesList(i)
    Next i
End Sub

• Create User Forms in Microsoft Excel
• Using Loops in VBA in Microsoft Excel
• The Best Shortcut Keys in Microsoft Excel
• Writing Your First VBA Function in Excel
• Add Combo Box to a Sheet in Microsoft Excel
• Formulas to calculate Loan payments in Microsoft Excel
• Array variables using VBA in Microsoft Excel
• Create an Amortization Schedule in Microsoft Excel
• Find duplicates items in combine columns in Microsoft Excel
• Vlookup Formula – Organizing the Data Table