Forum Discussion
ShazSh
Dec 05, 2022Brass Contributor
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...
- Dec 05, 2022
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
HansVogelaar
Dec 05, 2022MVP
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 = TrueShazSh
Dec 05, 2022Brass Contributor
Thank you very much Sir.