Forum Discussion

ShazSh's avatar
ShazSh
Brass Contributor
Dec 05, 2022
Solved

Run code on the rest of folders as well instead of single

This below code only works for Single folder what i am looking for is to go for all folder which are avaialble in the master folder.   I have 1 Master Folder which contains 10 to 15 subfolders in i...
  • HansVogelaar's avatar
    Dec 05, 2022

    ShazSh 

    Try this:

        Dim fso As Object, fld As Object, sfl As Object, fil As Object
        Dim strFolder As String, SubFoldNew As String
        Dim wbk As Workbook, wsh As Worksheet
    
        With Application.FileDialog(4)
            .Title = "Please select the Master folder"
            If .Show Then
                strFolder = .SelectedItems(1)
            Else
                MsgBox "You haven't selected a folder!", vbExclamation
                Exit Sub
            End If
        End With
    
        Application.ScreenUpdating = False
    
        Set fso = CreateObject(Class:="Scripting.FileSystemObject")
        Set fld = fso.GetFolder(strFolder)
        For Each sfl In fld.SubFolders
            SubFoldNew = sfl.Path & "\RecoveredWB"
            If Not fso.FolderExists(SubFoldNew) Then
                fso.CreateFolder SubFoldNew
            End If
            For Each fil In sfl.Files
                If LCase(Right(fil.Name, 5)) = ".xlsx" Then
                    Set wbk = Workbooks.Open(fil.Path, CorruptLoad:=xlRepairFile)
                    ' Is this loop needed?
                    For Each wsh In wbk.Worksheets
                    Next wsh
                    wbk.SaveCopyAs Filename:=SubFoldNew & "\" & wbk.Name
                    wbk.Close SaveChanges:=False
                End If
            Next fil
        Next sfl
    
        Application.ScreenUpdating = True

Resources