excel vba list of files in folder code example
Example 1: excel vba get list of Excel files in a folder
Public Function GetFiles(sPath$, Optional sFilter$ = "*.*")
Dim p&, f$, s$: s = Space(1e6)
f = Dir(sPath & _
String(Abs(Right(sPath, 1) <> "\"), "\") & sFilter)
Do While Len(f)
Mid(s, p + 1, Len(f) + 1) = f & vbLf
p = p + Len(f) + 1
f = Dir
Loop
GetFiles = Split(Left(s, p - 1), vbLf)
End Function
ExcelFiles = GetFiles("C:\temp", "*.xls*")
MsgBox LBound(ExcelFiles)
Example 2: excel vba list files in folder
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