Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

How to unprotect the excel sheet if forgot the password

Copper Contributor

Hi team need support to unprotect my sheet which is password protected and forgot the password

 

286 Replies
@NikolinoDE
I have tried the methods to open a password protected workbook but could not

Can you please help?

I was trying to access a password protected spreadsheet. 
I read through various replies here and came up with a very simple solution that worked for me.
- Save the protected Excel file as 97-2003
- Open in Google Drive
- Sheet is unprotected

the spreadsheet in question can be found here: 

Search: Private sector rents. Information on monthly rents paid in the private rented sector in Wales.Data Provider: Welsh Government Private sector rents by local authority, 1 January to 31 December 2019

@nickg2215 

On your fist step, how to save the protected excel file to version 97-2003?  I can't open the protected excel, it is blank screen, only a box that I need to enter a password.  Using rename function?

@BrockT 

Removed the underscore and extra spaces between the next line

 

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

Remove the underscore and spaces

.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

@NikolinoDE 

 

Hello I did some modifications ( getting log file) in hope that the password will be retrieved but alas it ain't working. Please have a look at it if I'm missing something 

 

Option Explicit

Sub GetPass()
    Const a = 65, b = 66, c = 32, d = 126
    Dim i#, j#, k#, l#, m#, n#, o#, p#, q#, r#, s#, t#
    Dim logPath As String
    logPath = "C:\logs\password_attempts.txt"
    With ActiveSheet
        If .ProtectContents Then
            On Error Resume Next
            For i = a To b
                For j = a To b
                    For k = a To b
                        For l = a To b
                            For m = a To b
                                For n = a To b
                                    For o = a To b
                                        For p = a To b
                                            For q = a To b
                                                For r = a To b
                                                    For s = a To b
                                                        For t = c To d
                                                            Open logPath For Append As #1
                                                            Print #1, Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)
                                                            Close #1
                                                            .Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _
                                                            Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)
                                                        Next t
                                                    Next s
                                                Next r
                                            Next q
                                        Next p
                                    Next o
                                Next n
                            Next m
                        Next l
                    Next k
                Next j
            Next i
            MsgBox "Finished"
        End If
    End With
End Sub

 

The lines that say:
.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & _

Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

...need to be joined together into one by deleting _ and all the spaces. I think they've just been split so that the code fits on the screen. So it/they need to read:
.Unprotect Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(n) & Chr(o) & Chr(p) & Chr(q) & Chr(r) & Chr(s) & Chr(t)

It didn't work for me until I did this - then it was so quick...

@naylorpd 

 

I know that, but my issue is that the password for unlocking is not getting shown in log file 

@NikolinoDE  I have ran the macro, but when I open the new file it is blank. Is it possible since my file of from 2017 there is a problem with my excel version? 

@NikolinoDE 

Please can you help me how to unprotect my excel spreadsheet? I forgot the password. I tried with the codes you suggested. But it couldn't perform. Shall I send you the spreadsheet and will you unprotect it please?

I have a file that I would love for you to try and open for me. It's password protected worksheet. How can I send it to you? Appreciate any help.
@Nikolino - I realize this is an old thread but, how long can I expect it to run? I have created a test file that I can use to try the process. The password I used is 10 letters long with two of them being capitalized. Am I looking at tens of minutes, hours or days? I just don't know how long to let it run before I conclude that it's not going to work. Thank you.

Hello @NikolinoDE 

I am a new user, and don't know how to send a file, please advise oh sharing the file

@NikolinoDE 

 

I ran the code you specified to remove the password and got the message box stating "Finished". However, the password still remains on the worksheet and the message box never told me what the password was. Any suggestions?

 

@MargaretMP 

  1. Make a copy of the Excel file and perform the following steps in your copied file.
  2. Open the file copy with 7-Zip.
  3. Go to the folder “xl”.
  4. Open the subfolder “worksheets”.
  5. Right-click on the file “sheet1.xml” (or the file for the worksheet in question) > “Edit” to launch the editor.
  6. Find the XML entry “sheetProtection”.
  7. Delete this XML entry.
  8. Save, close, and archive the XML file and close 7-Zip.

@vedangbapna it's easy, you can try catpasswd.com online recovery forgot password

I never could get this to work. I found the same solution that others have found and it worked perfectly for me. Here are the instructions that I typed up for it:

Removing worksheet protection from Excel files

 

  1. Start by making a copy of the file you want to open. You can do this by right-clicking on the file and selecting “Copy” from the drop-down menu.
  2. Click on the copy of your file to rename it. Change the extension at the end of the file from “.xlsx” to “zip.”
  3. Right-click on the .zip file and select “Extract All” from the drop-down menu.
  4. A pop-up window may appear to ask where you would like to save your extracted file. Choose your desired location and click the “Extract” button.
  5. After you click the “Extract” button, a new pop-up window may appear that shows the contents of the file. Locate the “XL” folder and open it.
  6. Next, locate the “Worksheets” folder and open it. This folder contains all the individual worksheets housed within your Excel spreadsheet. The names of these worksheets typically appear as “sheet1.xml,” “sheet2.xml,” “sheet3.xml,” etc.
  7. Right-click on “sheet1.xml,” click on “Open With” from the drop-down menu and select “Notepad.”
  8. Once the Notepad application opens, scan the code until you find a line that begins with the following:
    “<sheetProtection…”
  9. Identify where this code ends by looking for the first “>” that follows it. Then, highlight and delete that entire line of code, including the opening (“<”) and closing (“>”) brackets.
  10. Save the modified XML file by clicking the “File” button in the upper left corner of your Notepad application and selecting “Save” from the drop-down menu.
  11. Repeat steps 7 through 10 for each XML worksheet file.
  12. Select each of the modified XML worksheet files. Then, drag and drop them into the corresponding folder of the .zip file so that the modified files will overwrite the original unmodified XML files.
  13. Click on the .zip file and rename it by changing the extension at the end back from .zip to .xlsx. Then open the Excel file as usual. The worksheet protection should now be gone.

@MattMarusak 

Password options

 Microsoft informs

You can also find these instructions as a description in these links. As for the code, all I can say is that it has always worked for me...when it comes to worksheets.