Unprotect all sheets in all binary workbooks in a folder and save them unprotected

Question

I have a folder with .xlsb workbooks.
Each workbooks has 5 sheets, all of them protected with a password.
The password is common for all sheets, for all workbooks.
The workbooks themselves have no password.

How can I unprotect all the sheets of all the workbooks and saved them as unprotected?

I found the following code but it fails to do what I need (it works for only the active workbook).

Sub unprotect_all_sheets() 
On Error Goto booboo 
unpass = InputBox("password") 
For Each Worksheet In ActiveWorkbook.Worksheets 
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
booboo: MsgBox "There is s problem - check your password, capslock, etc."
End Sub

Try this http://stackoverflow.com/questions/13996740/import-password-protected-xlsx-workbook-into-r — Karthik Arumugham, Feb 22 '17 at 14:51
In R you could use `xl.read.file` from `excel.link`, for reading password-protected spreadsheets. http://stackoverflow.com/questions/34233738/how-to-read-an-xls-file-that-is-encrypted-with-r — Sam Firke, Feb 22 '17 at 14:54
@Brad Your link is about protecting, not UNprotecting. Please withdraw the duplicate sign. — user3507584, Feb 22 '17 at 14:59
Have you tried anything yourself? Sounds like you just found code on the internet, it didn't work, so came asking here. — luke_t, Feb 22 '17 at 15:32

brettdj · Accepted Answer · 2017-02-22T15:41:27.310

Something like this:

Any fails to unprotect the sheets are reported in the immediate window

Sub LoopThroughFiles()
    Dim StrFile As String
    Dim Wb As Workbook
    Dim ws As Worksheet
    Dim strFol As String
    Dim strPass As String

    'password
    strPass = "tested"

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    'folder to work in
    strFol = "c:\temp\"

    StrFile = Dir(strFol & "*.xls*")
    Do While Len(StrFile) > 0
        Set Wb = Workbooks.Open(strFol & StrFile)
            For Each ws In Wb.Worksheets
            On Error Resume Next
            ws.Unprotect strPass
            If Err.Number <> 0 Then Debug.Print strFol & StrFile & " " & ws.Name
            On Error GoTo 0

        Next
        Wb.Save
        Wb.Close
        StrFile = Dir
    Loop

     With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub

Unprotect all sheets in all binary workbooks in a folder and save them unprotected

1 Answers1