I dont know the password, how do I unlock Protected Cells?

English
, , , , ,
#1

I’m trying to do my first shipment to Amazon, through FBA. I’m stuck on the step of the packing list. I dont have Microsoft Excel, which is their preferred software, but my laptop came with LibreOffice. Downloading and opening the amazon pack list works through LibreOffice, but I’m supposed to put the number of each product into the column of which box the products will be shipped in. Problem is, I can’t enter anything into this spreadsheet. It just says “Protected Cells can’t be modified”.

I’ve read several different ways to remove protection, “Tools/Protect Documents/ unselect anything that is selected”.
That requires a password which I do not know. I dont know how to reset a password if I chose one way back when I got the laptop and used this program the first time and forgot it. I dont see a way to reset the password.

Other attempts to bypass the password dont work. I heard saving the document again and making sure “password protected” is not selected. That didn’t work.

Control+ Shift+ T… doesn’t seem to do anything.

Anyone else run into this? Any other suggestions?

#2

Did you use a template provided by amazom? if so, please set a link to the download location.
Internal protection (which is what we are talking of) is weak. I never did it for a file in an MS format, but I know from experience that removing the protection by file manipulation is not difficult for .ods files. I would like to also try it for an Excel file.

Also: High “Macro Security” is set by default for LibO. This setting will block even a prompt for allowing macros.

#15

Btw, there’s now a How to remove a Password Protected Sheet Flag FAQ.

#3

If a spreadsheet has been protected by a password then that password is specific to that spreadsheet. You have not set one in any global settings and forgotten it.

There are two modes for cell protection. One without setting a password is so that cells are not accidentally changed. When setting protection in this way you leave the password fields blank. When you unset protection you will not be asked for a password.

The other way when you want to use a password you set up the password when you set protection and require this password to unprotect.

In your case it is unlikely that Amazon will have set a password without telling you what it is. Select one of the protected cells and go to Tools > Protect Document > Sheet and if there is no request for a password the cells should be unprotected. It is confusing that whether you are protecting or unprotecting the Menu always shows Protect Document even if protection is on and you want to unprotect it.

#4

It seems the user cannot enter values directly into the relevant cells. This may mean that someone created a VBA framework to run the sheets. This probably being another case of pompous obfuscation of banality. I would really want to see an example.

#5

While the question was about a specific case, let me answer generally on the title question: how to unprotect a sheet which has been protected by password, but otherwise not unreadable (so not competely encrypted by a password).

Cell protection password is just a friendly hint that we shouldn’t edit the cells. Still, LibreOffice do not seem to offer to disable this hint, so you need a bit of hacking. Nothing really serious, but basic computer knowledge is required, and unix/linux tools help tremendously.

Take the ODS file, and simply unzip it into a directory.

You will see lots of stuff, among them the content.xml file, which contains all the data for the sheet. This file contains also the protection, in the form of XML attributes. We simply have to mess with them. The laziest way is to replace the protection flag:

sed -e 's/table:protected="true"/table:protected="false"/g' < content.xml > tmp ; mv tmp content.xml

then zip it again and rename to something.ods. When loading it LibreOffice will complain that the file is invalid and should be fixed, so let loffice do the work. The result is a sheet with no protection and hopefully all data intact.

If you’re not the unix type you should find some program which can replace the string in the content.xml while not breaking its structure (you can reformat it, it’s basically a text file, but don’t break words). zipping and unzipping generally works everywhere.

#6

Unfortunately it didn’t work for me. got the message The file '$(ARG1)' could not be repaired and therefore cannot be opened. and that was that.

#7

First, sed -e 's/table:protected="true"/table:protected="false"/' < content.xml > tmp ; mv tmp content.xml seems to be enough. Second: you should not zip it into a dir, so stand in the dir and do zip -9v ../unprot.ods * from there. It should work, jus checked the latest 6.0.xx.

#8

Replacing everything ‘protect’ by an arbitrary ‘ohplease’ is completely ill-advised. See the FAQ I linked in my answer.

#9

@erAck: offering your wisdom more than one year after isn’t quite, um, polite. Also, you’re wrong, as it works absolutely well. And you seem to have completely ignored the update. Oh well.

#10

What’s your problem? That I wrote the FAQ three months ago and just now came across this question? Would you prefer if I did not add the information?

And you removed the ill-advise just in your last edits which was after I added the comment.

#11

My problem is the form you have chosen. First calling “Ill advised” a method which worked for the past year (and you could have checked that it does). Then downvoting instead of advising (which is bad since without karma it’s not possible to do a lot of things which would be required for everyday stuff). Third, even you see how comments should work: you make a suggestion and it may or may not be followed, but you have chosen a different track.
So you could have added your answer and let the community upvote it, if it’s better, for example.

#12

Your original advice was 's/protect/ohplease/g' that is ill-advised and by downvoting I prevented it to show up on top of the list.

#13

If it makes you happy I’ll remove the downvote now that you changed things.

#14

Thank you so much! It worked great for me!
I was using 19_1040.ods to estimate my taxes, but I kept getting, “Protected cells can not be modified.” message even though the author said he didn’t protect the sheet! Well, after running the sed command you provided, I was able to get on with my mission. LibreOffice did detect corruption and offered to fix it, so I told it to do so and everything worked flawlessly thereafter! I am sorry I cannot upvote you since I don’t have enough karma after just joining to post this. You resolved my problem!

#16

I was able to solve this. Here is how I did it. I am using Ubuntu 18.04.

Assume that the file whose password protection we want to remove is AttendanceReport.ods. Assume also that it is in the current directory. All commands below should be made in the directory where AttendanceReport.ods is present.

First create a copy of the file in a temporary directory.

 $ mkdir WorkArea
 $ cp AttendanceReport.ods WorkArea/
 $ cd WorkArea/

Unzip contents of AttendanceReport.ods

 $ unzip AttendanceReport.ods

Examine the contents of the file “content.xml” and search for the string "table:protected"

 $ less content.xml

 Within the less command press '/table:protected' (Do not include the single quotes.)

 You should find a long string containing something like the following. Copy and paste it to a convenient open editor.

 table:protected="true" table:protection-key="KFLVezSiI9yL3/pl1z6FnxYrTbQ=" table:protection-key-digest-algorithm="http://docs.oasis-open.org/office/ns/table/legacy-hash-excel"

 Yours will look different but the idea is to copy the attributes table:protected, table:protection-key, table:protection-key-digest-algorithm.

 Press q to leave the 'less' command.

Remove the attributes that protect the spreadsheet. You should still be in WorkArea/. Give the following command.

 $ sed -i 's|table:protected="true" table:protection-key="KFLVezSiI9yL3/pl1z6FnxYrTbQ=" table:protection-key-digest-algorithm="http://docs.oasis-open.org/office/ns/table/legacy-hash-excel"||g' content.xml

Freshen the file AttendanceReport.ods with the new content.xml

 $ zip -f AttendanceReport.ods content.xml

The file AttendanceReport.ods in WorkArea/ should now have their cells unprotected.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.