Get a list of files in VBA


There are two ways to get a list of files inside a folder with VBA. The first one is the Microsoft Scripting Library way, where the FileSystemObject is used. The second one is the "traditional" Dir command which returns a file each time the function is called.
'the scripting lib way
Public Function GetListOfFiles(path As String, ParamArray Extensions()) As String() Dim fs As New FileSystemObject Dim fld As Folder: Set fld = fs.GetFolder(path) Dim fl As file Dim list() As String, count As Long: count = 0 For Each fl In fld.files Dim ext As String, extIncluded As Boolean, i As Long ext = fs.GetExtensionName(fl.name) extIncluded = False For i = 0 To UBound(Extensions) If LCase(ext) = LCase(Extensions(i)) Then extIncluded = True Exit For End If Next 'If ext = "xls" Or ext = "xlsb" Or ext = "xlsx" Or ext = "xlsm" Then If extIncluded Then count = count + 1 ReDim Preserve list(1 To count) list(count) = fl.path End If Next GetListOfFiles = list End Function

If we want to avoid using the Microsoft Scripting Runtime, we may use the Dir command which seems to be convenient but only for small tasks.
Public Function GetListOfFiles(path As String, ParamArray Extensions()) As String()
   Dim list() As String, count As Long: count = 0
   
   If Right$(path, 1) <> "\" _
      Then path = path & "\"
   
   Dim iExt As Long
   For iExt = 0 To UBound(Extensions)
      Dim fl As String
      fl = Dir(path & "*." & Extensions(iExt))
      
      Do While Len(fl) > 0
         count = count + 1
         ReDim Preserve list(1 To count)
         list(count) = path & fl
        
         fl = Dir
      Loop
   Next
   
   GetListOfFiles = list
End Function

A sample of use follows where both functions may be used. The path may be given with or without a trailing slash. The extensions should be given without the period. To retrieve all files just pass * as the Extensions parameter.
Public Sub test()
   Dim files() As String
   files = GetListOfFiles("D:\charts", "gif")
   Dim i As Long
   For i = 1 To UBound(files)
      Debug.Print files(i)
   Next
End Sub

Comments

Popular posts from this blog

Write Unicode text using VBA

Calling Fortran intrinsic functions from Visual Basic

Dictionary class extensions (CopyTo, Sort) (C#)