List files in a folder with Office 97 or later

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

Document last updated 2000-02-04 12:49:08 Printerfriendly version

User comments:

Ole P. from Norway wrote (2005-06-16 20:30:35 CET):

Re: List additional info such as Date Created?
Yes, see this example.

William Tan from Singapore wrote (2005-06-16 08:30:02 CET):

List additional info such as Date Created?
Is it possible to list the date of file created next to the filenames?
I'm trying to list Zip files
appreciate yr help

Ole P. from Norway wrote (2005-04-19 10:14:17 CET):

Re: Does not list subfolders
This is a macro example that lists files in sub-folders.

This is a macro example that shows how to let the user select a folder.

You will have to use your imagination and put the two together.

BILLY SIWALE from Lusaka Zambia wrote (2005-04-19 08:56:12 CET):

Does not list subfolders
This is a good macro. But like the one from Microsoft IT DOES NOT LIST THE FILES IN THE SUBFOLDERS. It would be good to also include an INPUT BOX so that the user is prompted for the path to the folder.

CAN YOU HELP !!!!!!!!!!

Please send me a reply to billysaw10101@yahoo.com