Unprotect All Excel Sheets for All Workbooks in a Directory

Tags

excel, unprotect, unprotect worksheets, worksheet

This Excel macro came in very handy when I had a set of workbooks in a folder (and subfolders) that all needed unprotected before running another macro to combine all of the workbooks into a single file. Be sure to update the bolded variables below. Source.

Const cStartFolder = "C:\Documents and Settings\UserName\My Documents\Spreadsheets" 'no slash at end
Const cFileFilter = "*.xlsx"
Const cPassword = "CleverPassword" 'use empty quotes if blank

Sub UnprotectAllWorksheets()
    Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet

    ExtractFolder cStartFolder, arr()

    On Error Resume Next
    j = -1: j = UBound(arr)
    On Error GoTo 0

    For i = 0 To j
        Set wkb = Workbooks.Open(arr(i), False)
        For Each wks In wkb.Worksheets
            wks.Unprotect cPassword
        Next
        wkb.Save
        wkb.Close
    Next
End Sub

Sub ExtractFolder(Folder As String, arr() As String)
    Dim i As Long, objFS As Object, objFolder As Object, obj As Object

    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFS.GetFolder(Folder)

    For Each obj In objFolder.SubFolders
        ExtractFolder obj.Path, arr()
    Next

    For Each obj In objFolder.Files
        If obj.Name Like cFileFilter Then
            On Error Resume Next
            i = 0: i = UBound(arr) + 1
            On Error GoTo 0
            ReDim Preserve arr(i)
            arr(i) = objFolder.Path & Application.PathSeparator & obj.Name
        End If
    Next
End Sub

Advertisement