CODEWITHSUNDEEP
excelvba
rob
rob

Reputation: 317

Using VBA to Loop Through Files in Sub-folders

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

Upvotes: 0

Views: 2834

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

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

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 1

Related Questions