When Microsoft introduced Excel 2007, they introduced new file types – we all know them by now: xlsx, xlsm, xltx, etc. These file types are often referred to as Open XML. That’s because the new file types are essentially packages that contain XML files. If you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file.
The new Open XML file types come with lots of benefits. One of the major benefits is that you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.
Well, while playing with the Open XML files, I discovered that you can remove spreadsheet protection simply by applying a simple edit to the xml within the Excel file.
Say I have a workbook where Sheet1 is password protected. So I think to myself, “the nerve of some people – trying to keep me out of their spreadsheet”.
I decide that I want to unprotect this sheet, but I don’t know the password. Because this is Excel 2007, I’ll hack into the xml and remove the spreadsheet protection.
Step 1: Make a backup of your file in case you really monkey it up.
Step 2: Change the file extension to zip.
Step 3: Extract the contents of the zip file.
Step 4: Go to the extracted files and navigate to the xml for the target sheet (found in the ‘xl\worksheets’ directory)
Step 5: Open the target sheet’s xml document using an XML editor (I use a free editor called XML Marker)
Step 6: Find the ‘sheetProtection’ tag and remove the entire line.
Step 7: Save the edited xml document and replace the old xml document found in the original zip file.
Step 8: Change the extension back to xlsx.
Step 9: Enjoy your unprotected sheet.
That’s right folks; simply removing the sheetProtection element from the xml part negates all protections placed on that sheet. Amazing, right?
A couple of notes:
- Any password you see in the XML file is not the real password, nor will it work if you try to use it. It’s worthless.
- Unfortunately, it doesn’t seem as though you can unprotect an entire workbook this way. Something prevents you from even opening the Open XML package for a protected Workbook.
- Do I have to even mention that this doesn’t apply to any xls files?
- Of course, you could do this all programmatically, but this strikes me as a one-off kind of thing. So coding something up is just not worth it to me.
RELATED STUFF
It doesn’t work after changing the extension it gives error while extraction files. ” The archive is either in unknown format or damaged”. I am using office 2007 professional with sp1
need help
worked like a charm, thanks
Thanks a lot! It works!!! Always wanted to learn, never knew how to do it!
when i change the extension i didnt find an *.xml in the order can someone help me?
if i unzip my changed xlsx i found 3 things. 1 order named [6]DataSpaces 1 file named encryptedpackage and 1 file named encryptedinfo.
if someone have an idea, I would appreciate
thanks
The system will not allow me to delete said line – it seems to be protected as well -
Great, Excel 2007 works perfectly. many thx. like a xmas present.
Excellent!. Works fine. Hey, where do you get these knowledge man?
It Works!!! I work as an IT Assistant in an organisation but i gotta admit im impressed! Thumbs up!!
Great Mate!
Can’t get it to work. Unzipping an Excel file with NO protection and then, without changing any xml content, re-zipping it into a new file renamed to type xlsx results in “Excel found unreadable content” from Excel2007. Looks as though MS has put something into Zip to foil this – what version of Zip are you using?
Regards
Robert Nixon
Found a youtube demo of how to do this – the secret seems to be to use 7zip not Winzip. Tried it on a small file and it works, so I hope it’s ok with large ones.