Using VBA to Loop Through Files in Sub-folders

Question

I am trying to write a macro to loop through all files in all sub-folders in a folder to then call another macro to unzip the files. The unzip code works fine but the loop code isn't working.

From the E:\Downloads\data\ADVANCED\2020\Feb\1\file path there are 5 sub-folders, each containing a single file to unzip.

Can anyone see the problem?

Thanks

This is the loop code:

Sub LoopAllFilesInAFolder()

Dim fileName As Variant
fileName = Dir("E:\Downloads\data\ADVANCED\2020\Feb\1\")

While fileName <> ""

    Call UnZipFile

    fileName = Dir
Wend

End Sub

Does this answer your question? [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) — Geert Bellekens, Oct 09 '20 at 09:55
How does your UnZipFile Sub/Function know what folder files to unzip? Shouldn't it accept like argument the folder path? I am asking that in order to post an answer iterating between all subfolders and doing 'something' for the found folder. Your code does no seem to do anything in terms of unzipping, if the folder path is not passed to the called function... — FaneDuru, Oct 09 '20 at 11:03

FaneDuru · Accepted Answer · 2020-10-09T12:07:52.163

Try using the next approach, please:

Sub LoopAllFilesInAFolderAndSubfolders()
 Dim FSO As Object, foldName As String

 'Create FSO object
 Set FSO = CreateObject("Scripting.FileSystemObject")
 'Set the folder name to a variable
 foldName = "E:\Downloads\data\ADVANCED\2020\Feb\1\"
 'Call the recursive itSubFolders Sub, which makes the job:
 itSubFolders FSO.GetFolder(foldName)
End Sub

And the recursive Sub extracting all files in subfolders:

Sub itSubFolders(FSOFolder As Object)
 Dim objSubfold As Object, objFile As Object

 'Iterate between al subfolders
 Sub itSubFolders(FSOFolder As Object)
 Dim objSubfold As Object, objFile As Object

 'Iterate between al subfolders
 For Each objSubfold In FSOFolder.SubFolders
    itSubFoldersAndFiles objSubfold
 Next

 'iterate for files
 For Each objFile In FSOFolder.Files
    'Debug.Print  objFile.path
    itSubFolders objFile 'I think, the code should send the file path as parameter, to the existing Function/Sub
 Next
End Sub

You can test the above recursive Sub, un commenting the line 'Debug.Print "Subfolder: " & objFile.path and commenting the following one. It will return in Immediate Window all files path.

Note: The above recursive Sub works also for all subfolders inside subfolders...

@rob: Still alive? You received two answers. Did you find some time to test them? — FaneDuru, Oct 10 '20 at 12:55

VBasic2008 · Answer 2 · 2020-10-09T12:32:36.850

Files in Subfolders of a Folder (`FileSystemObject`)

Test first with Debug.Print to see if the resulting list contains the exact files you need to unzip.
To remove any confusion, consider the following paths:
C:\Test1\Test2
C:\Test3\Test4
- If FolderPath = "C:", then the code will find files only in folders Test1 and Test3.
- It will not find files in C:, Test2 or Test4.

The Code

Option Explicit

Sub LoopAllFilesInAFolder()
    
    Const FolderPath As String = "E:\Downloads\data\ADVANCED\2020\Feb\1\"
    Const Extension As String = "zip"
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim fldr As Object
    Set fldr = fso.GetFolder(FolderPath)
    Dim subFldr As Object
    Dim fil As Object
    For Each subFldr In fldr.SubFolders
        For Each fil In subFldr.Files
            If StrComp(fso.GetExtensionName(fil.Path), Extension, _
                       vbTextCompare) = 0 Then
                Debug.Print fil.Path
                'UnZipFile fil.path
            End If
        Next fil
    Next subFldr
    
End Sub

' If you want to learn more about the File System Object, add a reference
' to it via 'VBE>Tool>Microsoft Scripting Runtime'.
' Now its intellisense will be enabled and you should use the following code:

Sub LoopAllFilesInAFolderLearn()
    
    Const FolderPath As String = "E:\Downloads\data\ADVANCED\2020\Feb\1\"
    Const Extension As String = "zip"
    Dim fso As New FileSystemObject
    Dim fldr As Folder
    Set fldr = fso.GetFolder(FolderPath)
    Dim subFldr As Folder
    Dim fil As File
    For Each subFldr In fldr.SubFolders
        For Each fil In subFldr.Files
            If StrComp(fso.GetExtensionName(fil.Path), Extension, _
                       vbTextCompare) = 0 Then
                Debug.Print fil.Path
                'UnZipFile fil.Path
            End If
        Next fil
    Next subFldr

End Sub

Using VBA to Loop Through Files in Sub-folders

2 Answers2

Files in Subfolders of a Folder (FileSystemObject)

Files in Subfolders of a Folder (`FileSystemObject`)