excel vba list all files in folder and subfolders
' List of files in folder / subfolders
' Needs to add "Microsoft Scripting Runtime" reference to your file
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