excel vba list files in folder and subfolders code example
Example: excel vba list all files in folder and subfolders
Sub FolderFilesInfo(ByVal pFolder As String, ByRef pColFiles As Collection, _
Optional ByVal pGetSubFolders As Boolean, Optional ByVal pFilter = "*.*")
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)
For Each oFile In oFolder.Files
pColFiles.Add oFile
Next oFile
If pGetSubFolders Then
For Each oSubFolder In oFolder.SubFolders
FolderFilesInfo oSubFolder.Path, pColFiles, pGetSubFolders, pFilter
Next
End If
End Sub
Sub TestMe()
Dim colFiles As New Collection, sFilePath As Variant
FolderFilesInfo ThisWorkbook.Path, colFiles, True, "*.txt"
For Each oFile In colFiles
Debug.Print oFile.Name & " : " & oFile.Path
Next oFile
End Sub