ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address