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
Hi,
Great tip.
One can obviuosky do this for XLS files as well by opening the XLS file, saving it as an XLSX file and then performing the steps as outlined above.
Does this mean that any worksheet password (3 or 16 alphanumerics) of any version can be broken into?
Yes, great tip. I’m hacking into your system as we speak…what excel file do you keep your upcoming Saturday bacon recipes in?
Wow, it’s embarrassing and looks like a step backward. I wonder if Excel 2010 continues this trend?
Step 7: Save the edited xml document and replace the old xml document found in the original zip file.
That step is really important, I tried to re-zip the extracted folder and then renaming the zip and it didn’t work.
As for 2010, it won’t change a thing since you can always do a save as in another format. If they block that in 2010, people will only have to do it in 2007.
And Excel 2002 and 2003 come with a built-in script editior.
As Dad used to say – locks are to keep the honest people out.
Great tip. But, wow, nothing about the events of Tuesday? Miracles? Vending machines and plastic recliners? Right into XML hacking, impressive. You are truly a force to be reckoned with, in the geek world. I figured the next tip would be an extension of John’s 6 ways to sell or some brilliant Access trick to track the insurance submissions. At any rate, congratulations.
Worksheet and Workbook protection continues to remain weak in 2007/2010
However the VBA Password in 2007/2010 cannot be hacked with a Hex Editor like in the previous versions
The File protection password in 2007/2010 is super strong….. and can only be hacked by brute force methods.
Michael: Tomorrows bacon recipe will be dedicated to the new arrival.
I was FULLY expecting a placenta recipe.
Mike,
Do you know how to encrypt the XML part of the Excel file. I have see this done but have no idea how
Sam: I actually don’t know how to encrypt the XML part of the Excel File. I can’t imagine Excel wouldn’t complain if you encrypted one of the XML parts.
Then again, maybe encryption is what goes on when you protect the entire workbook.
No…. what I have seen is a file which is protected….Only the XML part made unreadable….
Hi,
I wish to access the VBA Macro code of an .xlsm file. How do i go about it?
Hotguy: I believe the VBA components of the Excel package (xlsm) are still stored in a binary file. You won’t be able to get to these through XML.
This is great, Excel 2007 protection is not robust. Simple yet powerful way to break the code. Thanks a lot, u are the man!
Worksheet protection do not involve encryption of data. For those who believe this is a weakness, it was the same in previous version. Difference is that with XML format it is now human readable instead of being binary.
Password protection to open a file is different, 2007 offer much better encryption than in previous versions. To protect file that way do “save as”, then click “tools” in the save dialog (next to OK button), then choose “general options”. Entire file will then be encrypted.
Hi….this is superb….but how do i open a password protected xcel 2007 file???
After I changed file extension i tried to extract it, it gives me an error: “The archive is either in unkown format or damaged”. Please help me on this
Kamal: Any standard zipping software should work (Winzip and 7-zip are both free).
Which zip software are you using?
Greatings,
You just save me some $$ of buying stupid removal software. Thanks!!!!!!!
I changed the line in note. It seems easier, on some way I could not changed in xml editor…but any hove, mine many thanks. Greatings for Bill Geyts:)))
When trying to open zip file (originally xlsx with password open) winzip gives error “The archive is either in unknown format or damaged”
Can you please advice why this error is appearing.
This seems to be really exciting,
But i am getting error “The archive is either in unknown format or damaged”, how to overcome this, plz help.
I have tried with winrar & winzip both. Any other solution…..??
i am getting error “The archive is either in unknown format or damaged”, how to overcome this, plz help.
I have tried with winrar & winzip both. Any other solution…..??
when i am renaming the .xlsx to .zip it is not opening or even i not able to extract that?what is the problem?
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
it is not working…i cannot extract the zip to other forms other than to its previous form like excel xlsx
am not able extract the zip file after renaming to .zip. pls help
worked perfect… thx!
here is what worked for me… in windows explorer, rename the file to .zip… then extract all the files to a folder, doesn’t matter where. edit the sheet1.xml and change the password to anything you want. (doesn’t matter). save it. in explorer, expand the zip file by clicking on the + sign. (dont open it using winzip) right mouse click and delete the sheet1.xml file from that directory. then drag and drop the changed file to the same directory it came from in the expanded zip file. rename the file back to xlsm or xlsx (or whatever it was), open it, then go to “REVIEW” and select unprotect sheet. It doesnt ask for a password. done. if it makes you feel better, protect sheet, add a new password, then save it. It re-builds the XML files and updates the directory information in the file…
OpenOffice.org 3…
Save xlsm as xls
Open with Calc and have full access to VBA source and all sheets.
In the folder xl there is a file workbook.xml with a tag filesharing remove the tag and it removes the workbook protection.
This did not work for me..After changing the extension it gives error while extraction files. ” The archive is either in unknown format or damaged”.
After step 3 the rename of the extension to ZIP –
Step 4 Winzip could not open the file- as it does not appear to be a valid archive.
you can email me a work around if there is one
Thx
i changed the[Excel 2007 workbook] .xlsx extension to .zip and when i extracted the file , i dint find any xml file . Do reply
gulshanKumar: You’ve got to look in the directory files that get extracted. The xml files are in the Worksheets file.
Followed the instructions but was unable to delete or cut the sheet protection line…there simply isn’t an option to delete……any ideas?
Brilliant. You’re a legend
just amazing …
thanks for sharing !
sebastien
hello sir, i have excel file which i protect from opening, i put password to open xlsx file , even if i try this methiod , while extract error occcur,” no archieve “…this works for sheet but how i can hack open password………..
kuttymanni@gmail.com
pls help me imp file
i cannot open my excel file due to password protected. Anybody can help me pls…….
Genius. I can’t believe they made it this easy.
if you get an error saying the excel file is corrupted, try the following.
a) extract the zip and get the updated worksheets (sheet1.xml, sheet2.xml etc).
b) now browse the original zip folder in windows explorer (not unzip) and then paste the updated file in the worksheets folder.
c) rename the zip file to xlsx file.
it is either there is nothing to unzip or the zip file is corrupted, so this trick doesn’t work if the file is password protected. I was able follow the steps on a file that is NOT password protected.
Also, if you have a whole workbook that’s protected, you can open /xl/workbook.xml and remove “” to remove the protection. This was on a docx saved by libreoffice (not the real office) so your mileage may vary.
The blog ate the tag in my last comment.
[less than]workbookProtection/[greater than]
thanks!!!
Thanks a lot!
Hi Friends,
Want to open password protected .xlsx file. Pls help.
sudeesh:
http://datapigtechnologies.com/blog/index.php/hack-into-a-protected-excel-2007-or-2010-workbook/
it did not work
help please