I had an Excel Workbook that had password protected sheets but I didn't know the password. Having a look online, I was able to find a password breaking / unprotecting VBA script which worked fine. The only issue I had now was that there was a total of 40 sheets that needed unprotecting. Running this script individually on each sheet would have been possible but seemed like a daunting task. Therefore, with my limited VBA skills I modified the script to cycle through all the sheets in the workbook and unprotects any sheets that were protected. It’s not the prettiest but it did the job beautifully and will hopefully prove useful to someone else.
Please note that this script will only work against the older .xls
file type and not the newer .xlsx
. One way to get around this is saving the workbook as the older type, run the script and then save the now unprotected workbook as the newer type. Though bear in mind that this may cause issues with formatting etc. due to the conversion process.
This is the modified VBA script that I came up with:
Sub Main()
Dim WS_Count As Integer
Dim WS_I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
'Loop through all the sheets
MsgBox "Starting..."
For WS_I = 1 To WS_Count
Call PasswordBreaker(WS_I)
Next WS_I
MsgBox "Done!"
End Sub
Sub PasswordBreaker(WS_I)
Worksheets(WS_I).Activate
Dim I As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For I = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(I) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
'If Sheet is not protected break out to try the next one
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub