Getting all finames from a path using Excel-VBA and recursion

Question

I'm trying to get a list of file-names, with a given file extension, from a selected-by-user path. My code:

Option Explicit
Public Sub GetFileNameListFromPath()
    Dim filesystem As Object
    Dim folderdialog As Object
    Dim path As Object
    Dim excel As Object

    Set excel = Application
    Set filesystem = CreateObject("Scripting.FileSystemObject")
    Set folderdialog = excel.filedialog(msoFileDialogFolderPicker)

    folderdialog.AllowMultiSelect = False
    folderdialog.Title = "Selecionar pasta"
        If folderdialog.Show <> -1 Then
            Exit Sub
        End If
    
Set path = filesystem.GetFolder(CStr(folderdialog.SelectedItems(1)))
    
    Dim files As New Collection
    GetFilesFromPath files, path, ".txt"
    
    Dim file As Object
    Dim i As Integer
    i = 0
    For Each file In files
        Cells(i + 1, 1) = file.Name
        i = i + 1
    Next
End Sub

Public Sub GetFilesFromPath(ByRef argfiles As Collection, ByRef argpath As Object, Optional ByVal extension As String)
    Dim subfolder As Object
    For Each subfolder In argpath.SubFolders
        GetFilesFromPath argfiles, subfolder
    Next
    Dim argfile As Object
    If IsMissing(extension) Then
        For Each argfile In argpath.files
            argfiles.Add argfile
        Next
    Else
        For Each argfile In argpath.files
            If Right(argfile.Name, 4) = extension Then
                argfiles.Add argfile
            End If
        Next
    End If
End Sub

Trying to get a list of all filenames in path, including those inside sub-folders, so that's reason for recursion. But, I'm only getting the first level sub-folder file-name. Any thoughts where I'm getting wrong?

Edit: I refactored the code including the awesome insights and answers. Seems working good now, with a "but": I need to run 2 consecutively, to get the list on cells... strange behavior...

    Option Explicit
Public Sub GetFileNameListFromPath()
    Dim FileSystem As Object
    Dim folderdialog As Object
    Dim path As Object
    Dim excel As Object

    Set excel = Application
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    Set folderdialog = excel.filedialog(msoFileDialogFolderPicker)

    folderdialog.AllowMultiSelect = False
    folderdialog.Title = "Select folder"
        If folderdialog.Show <> -1 Then
            Exit Sub
        End If
    
    On Error GoTo ErrorHandler
    Set path = FileSystem.GetFolder(CStr(folderdialog.SelectedItems(1)))
    
    Dim files As Collection
    Set files = New Collection
    GetFilesFromPath files, path
    
    Dim currentfile As Object
    Dim i As Integer
    i = 0
    For Each currentfile In files
        Cells(i + 1, 1) = currentfile.Name
        i = i + 1
    Next
    Exit Sub
ErrorHandler:
    MsgBox "Error during macro run..."
    Debug.Print Err.Number & Err.Description
    Err.Clear
    Exit Sub
End Sub

Sub GetFilesFromPath(ByRef outfiles As Variant, ByRef path As Variant, Optional ByRef extension As String)
    Dim subfolder As Object
    For Each subfolder In path.SubFolders
        If extension = vbNullString Then
            GetFilesFromPath outfiles, subfolder
        Else
            GetFilesFromPath outfiles, subfolder, extension
        End If
    Next
    
    Dim currentfile As Object
    If extension = vbNullString Then
        For Each currentfile In path.files
            outfiles.Add currentfile
        Next
    Else
        For Each currentfile In path.files
            If LCase(Right(currentfile.Name, Len(extension))) = LCase(extension) Then
                outfiles.Add currentfile
            End If
        Next
    End If
End Sub

See [here](https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-VBA) — cybernetic.nomad, Jun 30 '23 at 14:20
Just a quick PSA: don't use `Dim files as New Collection`, [instead `Set` it separately](https://stackoverflow.com/questions/2478097/vba-difference-in-two-ways-of-declaring-a-new-object-trying-to-understand-why). — Vince, Jun 30 '23 at 16:24
@Vince: As `files` gets out of scope at the end of `GetFileNameListFromPath` (and therefore is destroyed), I don't see the point. — FunThomas, Jun 30 '23 at 16:28
That's fair @FunThomas, but it's best not to get in the habit. Another quick PSA: `IsMissing()` is only intended for `Variant`s ([source doc](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ismissing-function)). @OP, your code looks fine to me. Maybe try adding some `Debug.Print` lines to see when it enters the recursive path? — Vince, Jun 30 '23 at 16:37
I don't get the habit to use global variables (where the `new` could be a problem) or implement them as kind of singleton so that the creation is hidden. — FunThomas, Jun 30 '23 at 16:43

score 3 · Accepted Answer · answered Jun 30 '23 at 14:51

3

In your subfolder loop, you're not passing the extension down.

Try

For Each subfolder In argpath.SubFolders
    GetFilesFromPath argfiles, subfolder, extension
Next

answered Jun 30 '23 at 14:51

CLR

11,284
1
11
29

1

Good catch. I would also suggest to change the line where you check the extension to `LCase(Right(argfile.Name, len(extension))) = LCase(extension)` so that you can search also for example `xlsx` and you catch also a file like `test.TXT`. – FunThomas Jun 30 '23 at 16:34
That's gold. I forgot that sometimes file extensions gets more than 3 characters. – Fernando Jun 30 '23 at 17:07

Getting all finames from a path using Excel-VBA and recursion

1 Answers1