SOLVED

Help with Protect Sheet

Copper Contributor

I used Protect Sheet to keep others from changing information on a spreadsheet. Apparently, the last time I updated the information, I entered my password too fast or something, because it is not working. I don't know how I could enter the same wrong password twice, but the password I usually use for this spreadsheet is not working. I tried different variations but nothing.

 

I don't want to have to completely recreate the spreadsheet. I tried making a copy of it, but that didn't work. I tried saving an unprotected copy, but that didn't work either. 

 

Is there a way for me to get a workable copy of this spreadsheet without starting over?

13 Replies
best response confirmed by BowenR1 (Copper Contributor)
Solution

As long as you're not talking about cracking an encrypted file, which would be illegal, and only talking about the restrict editing password as you say--then don't worry. Restrict editing passwords are very easy to remove in both Word and Excel.

 

Here is what you need to do. Make sure that you have enabled seeing file extensions in folders options. Then, change the file extension of the Excel to .zip from .xlsx. Next, open that zip file.

Where you go from here depends on which restriction you enabled.

 

If it was for the whole workbook:

  1. Open the workbook.xml file and find the xml node that begins "<workbookProtection" like this.
  2. Highlight from the < character through to the > character following the word "lockStructure" (inclusive) and delete it. That will unlock the workbook.
  3. Replace the xml in the zip with your changed file, and change the .zip back to .xlsx.

If it is just the sheet:

  1. Look in the xl folder for the sheet.xml file for your sheet.
  2. This time find the node beginning "<sheetProtection" through the > following the word "scenarios". Delete that, and this will unlock the sheet.
  3. Replace the xml in the zip with your changed file, and change the .zip back to .xlsx.

I've written a PowerShell script that will ask you to point it at a folder and loop through all the Word and Excel files therein, removing the restrict editing passwords to all of them. I can provide this to you if you like. I am unable to sign my scripts though, so I understand if you'd rather not run a strangers code on your machine. That said, I am not malicious, and if you'd like to use it I am willing to provide it to you. Nobody else uses it, and I wrote it just to see if I could while messing around with PowerShell.

@Rich89 

 

No...it is not for a malicious purpose. I'm just trying to save myself the time and headache of recreating the spreadsheet.

 

I have made it this far...Look in the xl folder for the sheet.xml file for your sheet. This time find the node beginning "<sheetProtection" through the > following the word "scenarios". Delete that, and this will unlock the sheet.

 

I'm not really good with computer speak and don't know a lot about excel. I only protected the worksheet, so I opened the xl file. There isn't a "sheet.xml" file, but there is a "workbook.xml" file. Where would I find the node? 

 

I am currently attempting to do this. I'm hoping you are still online.

 

Thank you!

The workbook one you only need if you protected the whole workbook. If it was the sheet, then after you open the zip, there’s a folder called xl, and inside that somewhere is the sheet xml file that you need. One sec i can post a picture if that would be helpful.

@Rich89 

This is what is inside xl file...

Alrighty.  I've attached some images here to assist you in the order that you need to do them.  These images are assuming you have restricted the sheet and not the whole book. When you look in the worksheets folder, you'll see however many sheet.xml files there are as you have in your workbook.  Just be sure to choose the correct one.  They don't correspond by name, only by order across the bottom of your Excel view.  Okay?  So, for example, if the sheet you restricted is the 3rd tab, you want sheet3.xml.

 

What you do is drag the xml file to your desktop, open it with notepad, delete that highlighted bit I'm showing in the one image, save, then drag back into the original location, overwriting the original.

 

Then close out of the zip and rename it back to .xlsx file extension. @BowenR1 

I found the sheet and "<sheetProtection algorithmName=...", but there is no "scenarios".

Now what? :(
This is what I found...

etProtection algorithmName="SHA-512" hashValue="0y8I1nW5880WNsyH+bqK5hWSNfLCcBWxl0st+jw//y64jBxqNUdGncS7QZTwR/DnqwcGjOqpmRLIWtDp5AUxHw==" saltValue="+j12ZWsg7o3dKbkM1nHCgQ==" spinCount="100000" sheet="1" selectLockedCells="1" selectUnlockedCells="1"/>

Okay. Make a copy of your Excel before you make any changes. Then delete all of:
<sheetProtection algorithmName="SHA-512" hashValue="0y8I1nW5880WNsyH+bqK5hWSNfLCcBWxl0st+jw//y64jBxqNUdGncS7QZTwR/DnqwcGjOqpmRLIWtDp5AUxHw==" saltValue="+j12ZWsg7o3dKbkM1nHCgQ==" spinCount="100000" sheet="1" selectLockedCells="1" selectUnlockedCells="1"/>

That should do it. I think this scenario might be slightly different from what I described because of what specifically was locked. But just delete that bit and try it.

IT WORKED!!!!!!

Thank you sooooo much!!!!!

I appreciate it more than you will ever now!

Enjoy the rest of your Sunday!
I'm very happy to help! And you've helped me as well. I've learned there are exception to how the xml node reads and that gives me a project to do on my script because my script would not have worked here.

Have a great day. :)
Oh, and don't forget to mark your thread as solved. That way in the future if someone comes looking they can find your solution.
LOL...how?
There should be an option under the posts to "mark as best response" I believe. Pretty sure that's what does it. I don't think it really matters which one you choose in this case since it was just you and I responding to each other.
1 best response

Accepted Solutions
best response confirmed by BowenR1 (Copper Contributor)
Solution

As long as you're not talking about cracking an encrypted file, which would be illegal, and only talking about the restrict editing password as you say--then don't worry. Restrict editing passwords are very easy to remove in both Word and Excel.

 

Here is what you need to do. Make sure that you have enabled seeing file extensions in folders options. Then, change the file extension of the Excel to .zip from .xlsx. Next, open that zip file.

Where you go from here depends on which restriction you enabled.

 

If it was for the whole workbook:

  1. Open the workbook.xml file and find the xml node that begins "<workbookProtection" like this.
  2. Highlight from the < character through to the > character following the word "lockStructure" (inclusive) and delete it. That will unlock the workbook.
  3. Replace the xml in the zip with your changed file, and change the .zip back to .xlsx.

If it is just the sheet:

  1. Look in the xl folder for the sheet.xml file for your sheet.
  2. This time find the node beginning "<sheetProtection" through the > following the word "scenarios". Delete that, and this will unlock the sheet.
  3. Replace the xml in the zip with your changed file, and change the .zip back to .xlsx.

I've written a PowerShell script that will ask you to point it at a folder and loop through all the Word and Excel files therein, removing the restrict editing passwords to all of them. I can provide this to you if you like. I am unable to sign my scripts though, so I understand if you'd rather not run a strangers code on your machine. That said, I am not malicious, and if you'd like to use it I am willing to provide it to you. Nobody else uses it, and I wrote it just to see if I could while messing around with PowerShell.

View solution in original post