In Office 97 or later it's easy to get a list of filenames in a folder, optionally included any subfolders:

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