Loop through folder and applying code to all xlsx-CodePudding

Had a code that was able to apply protect workbook but didn't save after running a different sub routine. I'm trying to apply a macro to all xlsx files in a folder where I'm deleting two sheets and applying a protect workbook. When I run it without the sheet deletion, the code runs but it doesn't apply to any of the files inside the folder.

Sub LoopThroughFilesFolder()
Dim StrFile As String, wb As Workbook, ws As Worksheet
StrFile = Dir("C:\Users\user\Documents\Destroy\*.xlsx*")
Do While Len(StrFile) > 0

    For Each ws In Worksheets
    If ws.Name = "Summary Copying" Or ws.Name = "Sum Totals" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    Next
    
    ProtectBookStructure ThisWorkbook, "password"
    StrFile = Dir
Loop

End Sub

Fixed with this code

 Sub LoopThroughFilesFolder()

   Dim myfile As String
   Dim wb As Workbook, ws As Worksheet

Application.ScreenUpdating = False

    myfile = Dir("C:\Users\user\Documents\Destroy\*.xlsx*")
    
        Do While myfile <> ""
           
           Set wb = Workbooks.Open(fileName:=myfile)
            
            For Each ws In Worksheets
            If ws.Name = "Summary Copying" Or ws.Name = "Sum Totals" Then
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
            Next
          
           ProtectBookStructure wb, "password"
           
        wb.Close savechanges:=True
        myfile = Dir
    Loop
    
Application.ScreenUpdating = True

End Sub

CodePudding user response：

I recommend you check out the link posted by @DecimalTurn as a comment to your question.

A solution for your problem may look like this:


Sub LoopThroughFilesFolder()
    Dim strFile As String, wb As Workbook, ws As Worksheet
    strFile = Dir("C:\Users\user\Documents\Destroy\*.xlsx")
    
    Do While Len(strFile) > 0
        'Open the workbooks like this:
        Set wb = Application.Workbooks.Open(strFile, False, False)
        
        'In the following, use the "wb" variable to do what you want for the
        'opened workbook.
        For Each ws In wb.Worksheets
            If ws.name = "Summary Copying" Or ws.name = "Sum Totals" Then
                Application.DisplayAlerts = False
                ws.DELETE
                Application.DisplayAlerts = True
            End If
        Next
        ProtectBookStructure wb, "password"
        
        'Save and close
        wb.Close SaveChanges:=True
        strFile = Dir
    Loop
End Sub

This code may still not work depending on how ProtectBookStructure looks like or if any of the workbooks in the folder are already password protected.