Lag en liste over filer i en mappe (Excel97 eller senere)

I Office97 eller senere er det ganske enkelt å lage en liste over alle filnavn i en mappe, eventuelt inkludert undermapper :

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

Dokumentet er sist oppdatert 2000-02-04 12:34:02 Utskriftsvennlig versjon

Brukerkommentarer:

Jan Nilsen fra Oslo skrev (2006-08-25 14:46:14 CET):

Dette er jo helt konge
Imponert over hvor proff denne koden er. Veldig lesbar og enkel.
Tusen takk for bra koder og flott side.

Kent skrev (2004-02-29 19:15:58 CET):

Mycket användbar Function
Tackar för en mycket bra hemsida!