Solved Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

[arnelgp]

it is a Sub, so it will not Return anything.
what you need is to pass your Collection to the Sub
and the Collection will be "filled" when the
Sub finished:

Dim fpath As String
Dim col     As New Collection

fpath = "\\Drive\Home\Desktop\"

'arnelgp
Call RecursiveDir(col, fpath, "*.*", True)

Debug.Print col.count
  'returns zero every time, fpath is valid and has several files/folders

 

[FrostByte]

Hi,

Thanks for your reply @theDBguy

Your tool worked good for me when I tested your specific application. But converting the code to my project was unsuccessful.

Maybe its my implementation at the caller level. I like your design as it returns strings instead of a collection of objects. Strings are simple and nice.

Anyway, after reviewing these examples of Recursion, I just decided to write my own so I understand how its works. And it works flawlessly for me. I welcome you to test the code if you please.

Thanks for your help and others who contributed.

Sub Test()
Dim c       As New Collection
Dim fil     As file
Dim i       As Integer
Dim path As String

path = "\\Drive\Home\Desktop\Test"

Set c = ListDir(c, path, "jpg")

For i = 1 To c.count
    Set fil = c.Item(i)
    Debug.Print fil.path
Next i

End Sub


Function ListDir(ByRef Col As Collection, path As String, Optional FExt As String) As Collection
'@IronFelix717 - 9.11.21    access-programmers.co.uk
'LISTS ALL FILES IN A ROOT FOLDER AND ALL SUBFOLDERS, RECURSION METHOD
'COL = Target collection to store FSO.file objects
'OPTIONAL: PASS EXTENSION (EX:  ".JPG" or.. "JPG")
Dim FSO         As New FileSystemObject
Dim Fold        As Folder
Dim SubFold     As Folder
Dim fil         As file
Dim SubFolds    As New Collection
Dim i           As Integer
Dim tempc       As New Collection

If FSO.FolderExists(path) Then
    Set Fold = FSO.GetFolder(path)

    For Each fil In Fold.files
        If FExt <> "" Then
            'CLEAN UP
            If InStr(1, FExt, ".") > 0 Then
                FExt = Mid(FExt, 2, Len(FExt))
            End If
              
            If FSO.GetExtensionName(fil.name) = FExt Then
                Col.Add fil
            End If
        Else
            Col.Add fil
        End If
    Next fil
          
    For Each SubFold In Fold.SubFolders
        SubFolds.Add SubFold
    Next SubFold
  
    For i = 1 To SubFolds.count
        Set tempc = ListDir(Col, SubFolds.Item(i), FExt)
    Next i
    Set ListDir = Col
End If

End Function

I get a complie error:

User-defined type not defined

This is

 

[FrostByte]

Hi,

Thanks for your reply @theDBguy

Your tool worked good for me when I tested your specific application. But converting the code to my project was unsuccessful.

Maybe its my implementation at the caller level. I like your design as it returns strings instead of a collection of objects. Strings are simple and nice.

Anyway, after reviewing these examples of Recursion, I just decided to write my own so I understand how its works. And it works flawlessly for me. I welcome you to test the code if you please.

Thanks for your help and others who contributed.

Sub Test()
Dim c       As New Collection
Dim fil     As file
Dim i       As Integer
Dim path As String

path = "\\Drive\Home\Desktop\Test"

Set c = ListDir(c, path, "jpg")

For i = 1 To c.count
    Set fil = c.Item(i)
    Debug.Print fil.path
Next i

End Sub


Function ListDir(ByRef Col As Collection, path As String, Optional FExt As String) As Collection
'@IronFelix717 - 9.11.21    access-programmers.co.uk
'LISTS ALL FILES IN A ROOT FOLDER AND ALL SUBFOLDERS, RECURSION METHOD
'COL = Target collection to store FSO.file objects
'OPTIONAL: PASS EXTENSION (EX:  ".JPG" or.. "JPG")
Dim FSO         As New FileSystemObject
Dim Fold        As Folder
Dim SubFold     As Folder
Dim fil         As file
Dim SubFolds    As New Collection
Dim i           As Integer
Dim tempc       As New Collection

If FSO.FolderExists(path) Then
    Set Fold = FSO.GetFolder(path)

    For Each fil In Fold.files
        If FExt <> "" Then
            'CLEAN UP
            If InStr(1, FExt, ".") > 0 Then
                FExt = Mid(FExt, 2, Len(FExt))
            End If
               
            If FSO.GetExtensionName(fil.name) = FExt Then
                Col.Add fil
            End If
        Else
            Col.Add fil
        End If
    Next fil
           
    For Each SubFold In Fold.SubFolders
        SubFolds.Add SubFold
    Next SubFold
   
    For i = 1 To SubFolds.count
        Set tempc = ListDir(Col, SubFolds.Item(i), FExt)
    Next i
    Set ListDir = Col
End If

End Function

I get a compile error:

user-defined type not defined.


"Dim fil As file"

 

[moke123]

I get a compile error:

user-defined type not defined.


"Dim fil As file"

Did you set a reference to MS Scripting Runtime?