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
Are there any code examples left?
New code examples in category VBA
-
VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
-
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
-
VBA 2022-03-27 17:05:52 vba simple quote
-
VBA 2022-03-27 14:30:19 textbox find and replace vb
-
VBA 2022-03-27 09:10:21 nested if else in vb.net
-
VBA 2022-03-27 09:10:09 excel vba string to bits
-
VBA 2022-03-25 02:00:15 vba verticalalignment
-
VBA 2022-03-24 12:20:06 Excel web scraping
-
VBA 2022-03-24 10:11:00 excel vba BitToLong
-
VBA 2022-03-21 18:00:05 excel vba save file txt