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

[Pat Hartman]

If you still don't have this working, here's another working example. Press the "Show me the Tool" button on the second form. This sample is about Word automation so has nothing to do with your problem. The "tool" is a form I used to read through a bunch of word docs to collect all the bookmarks. There is an option group on the form that tells whether to search just the selected folder or drill down to all folders below the chosen one. Uploading the whole thing means I don't have to extract the relevant parts and risk breaking the "tool" in the process.

 

[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?