Unprotect All Excel Sheets for All Workbooks in a Directory
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
[...] accomplished this with the below Excel macro code. Additionally, my worksheets were protected, so another script was used to perform this process en [...]
ReplyDeleteThat did work for unprotecting. Now how do I protect them?
ReplyDeleteOn line 5, change the sub to more accurately describe it (if you'd like):
ReplyDeleteSub ProtectAllWorksheets()
On line 17, change to the below:
wks.Protect cPassword, True , True
This will protect every worksheet with the password stated in the cPassword variable at the beginning of the script.
Great! Thanks for this!!!
ReplyDeleteHello, your macro works great. I have a problem.
ReplyDeleteOne of my worksheets in the file was accidentally saved with a different password than the rest. Is there anyway I can build it in to the macro? It is the forth of five sheets.
If this is only in one workbook it would be much easier to just change/remove the password from that sheet before running the script. If multiple workbooks that all have a known number of worksheets, I would change the workbook unprotect loop to a series of unprotect commands.
ReplyDeleteI applied the Macro on 187 workbooks.
ReplyDeleteI tried to add a specific command to that particular worksheet and it did not work.
But since I added
On Error Resume Next
The macro was able to ignore that sheet and go on to the next and now I ran your macro once more just for that missing sheet. It worked like a charm.
Thank you. again.
I have a similar scenario which is.....I have over 100 excel workbooks that have the same password (only one worksheet per workbook). I'd like something like this to where I can enter the password one time and it will open all workbooks. Any help appreciated!
ReplyDelete