Access/VBA Library

FastFileList

Returns an Array containing a list of files of a specified folder matching a specified filter.

Syntax

FastFileList( Foldername [, Filter] [, SubFolders] )
Parameters
Name Type Description
Foldername String Required. Name of the folder to be searched.
Filter String Optional. Filter to used to match files.
Default: "*.*"
SubFolders Boolean Optional. Indicates whether subfolders should be searched also.
Default: True
Return value

Variant : List of files found.

Code
Public Function FastFileList(ByVal Foldername As String, _
                             Optional ByVal Filter As String = "*.*", _
                             Optional ByVal SubFolders As Boolean = True)
    Dim TempFile As String
    Dim CommandString As String
    Dim Files As String
    
    If Right(Foldername, 1) <> "\" Then Foldername = Foldername & "\"
    
    With FSO
        '
        ' Name of temporay file
        '
        TempFile = .GetSpecialFolder(2) & "\" & .GetTempName
        '
        ' Define the command to be executed
        '
        CommandString = "CMD /U /C DIR " & _
                        Chr(34) & Foldername & Filter & Chr(34) & _
                        " /ON /S /B /A-D-H-S>" & _
                        Chr(34) & TempFile & Chr(34)
        '
        ' Remove subfolder parameter
        '
        If Not SubFolders Then
            CommandString = Replace(CommandString, "/S", "")
        End If
        '
        ' Execute command. Output in temporary file
        '
        With CreateObject("WScript.Shell")
            Call .Run(CommandString, 0, True)
        End With
        '
        ' Read content
        '
        With .OpenTextFile(TempFile, ForReading, False, TristateTrue)
            If .AtEndOfStream Then
                FastFileList = Array()
            Else
                FastFileList = Split(.ReadAll, vbCrLf)
            End If
            .Close
        End With
        '
        ' Clean up
        '
        .DeleteFile TempFile
    End With
    
End Function

Remarks

  • When at least one file has been found the last element of the result array will be empty.
  • When SubFolders is set to False the returned filenames does not contain the full path.
Example
  Dim FileList
  FileList = FastFileList("C:\")

  For Index = LBound(FileList) To UBound(FileList) - 1 ' Last element is empty!
      Debug.Print FileList(Index)
  Next Index