vba filter folders code example

Example: vba filter file list folder

' List of complete path of files in folder / subfolders
' Needs to add "Microsoft Scripting Runtime" reference to your file
Sub FolderFilesPath(ByVal pFolder As String, ByRef pColFiles As Collection, _
    Optional ByVal pGetSubFolders As Boolean, Optional ByVal pFilter As Collection)
    Dim sFolder As String
    Dim oFSO As New FileSystemObject
    Dim oFolder, oSubFolder As Folder
    Dim oFile As File
    
    sFolder = IIf(Right(pFolder, 1) <> "\", pFolder & "\", pFolder)
    Set oFolder = oFSO.GetFolder(sFolder)
    If Not ExistsInCollection(pFilter, sFolder) Then
        For Each oFile In oFolder.Files
            pColFiles.Add oFile
        Next oFile
        If pGetSubFolders Then
            For Each oSubFolder In oFolder.SubFolders
                FolderFilesPath oSubFolder.Path, pColFiles, pGetSubFolders, 
pFilter
            Next
        End If
    End If
End Sub

' Vba collection contains
Function ExistsInCollection(col As Collection, key As Variant) As Boolean
    On Error GoTo err
    ExistsInCollection = True
    IsObject (col.Item(key))
    Exit Function
err:
    ExistsInCollection = False
End Function
'------------------------------------------------------------------------------
Sub TestMe()
    Dim colFiles As New Collection, sFilePath As Variant
    Dim colExcludedFolders As New Collection
    With colExcludedFolders
        .Add "C:\test with spaces\subfolder 1\", "C:\test with spaces\subfolder 1\"
    End With
    FolderFilesPath ThisWorkbook.Path, colFiles, True, colExcludedFolders
    ' colFiles contains filtered files
    For Each sFilePath In colFiles
        With sFilePath
            Debug.Print .Path & " - " & .Name & " - " & .DateCreated
        End With
    Next sFilePath
End Sub