I wanted to add another sheet to an Excel workbook, but when I clicked on the "+" to add the additional worksheet, I saw the message "Workbook is protected and cannot be changed."
I didn't know what the password might be because I had never set a password for the workbook, which I had created in a prior version of Excel; I'm using Microsoft Office 2016 on a MacBook Pro running OS X El Capitan (10.11.6) at the moment.
The document was an .xlsx file, which is an Office Open XML (OpenXML) zipped, XML-based file format developed by Microsoft for spreadsheets, charts, presentations and word processing documents. You can change the file extension to .zip by renaming the file in the OS X Finder application and unzip it the way you would any other zip file. When you attempt to change the extension for the file, though the Finder, you will be prompted to confirm that you wish to do so; click on the "Use .zip" button. Or you can rename the file through a command line interface, e.g. a Terminal window.
$ cp ../MSGRS.xlsx MSGRS.zip $ unzip MSGRS.zip Archive: MSGRS.zip inflating: [Content_Types].xml inflating: _rels/.rels inflating: xl/_rels/workbook.xml.rels inflating: xl/workbook.xml inflating: xl/worksheets/sheet4.xml inflating: xl/worksheets/sheet1.xml inflating: xl/worksheets/sheet3.xml inflating: xl/worksheets/sheet2.xml inflating: xl/worksheets/_rels/sheet1.xml.rels inflating: xl/worksheets/_rels/sheet2.xml.rels inflating: xl/worksheets/_rels/sheet3.xml.rels inflating: xl/worksheets/_rels/sheet4.xml.rels inflating: xl/styles.xml inflating: xl/sharedStrings.xml inflating: xl/worksheets/sheet12.xml inflating: xl/worksheets/sheet8.xml inflating: xl/worksheets/sheet7.xml inflating: xl/worksheets/sheet6.xml inflating: xl/worksheets/sheet5.xml inflating: xl/worksheets/sheet9.xml inflating: xl/worksheets/sheet10.xml inflating: xl/worksheets/sheet11.xml inflating: xl/theme/theme1.xml inflating: docProps/custom.xml inflating: docProps/core.xml inflating: docProps/app.xml inflating: xl/calcChain.xml $ rm MSGRS.zip $
To unprotect the file edit the xl/workbook.xml file.
Within it you should see the following text:
<workbookProtection workbookPassword="xxxx" lockStructure="1"/>
xxxx will be an encrypted password. You won't be able to
simply use what use see for xxxx as the password, so simply
delete whatever text appears between the double quotes after
workbookPassword.
After you've deleted the password, you need to create a new zip file from the files you extracted from the original one. But you can't use the Finder or the zip utility available from the command line. The zip file produced by those methods on an Apple OS X system does not match what Excel is expecting - see Zipping and unzipping Excel xlsx files for further details. But you can recompress the directory and its contents using the Python script zipdir. Make sure you aren't including the original zip file in the new zip file, just the directories and files you extracted from the original zip file. Rename the new .zip file to the original file name extension, .xlsx.
$ python zipdir.py MSGRS_New ~/Documents/Work/MSGRS/temporary $ mv MSGRS_New.zip MSGRS_New.xlsx $
If you try to add a new sheet, you will again be warned that the workbook is protected and can not be changed. To unprotect the workbook, click on Tools, select Protection, then Unprotect.
You should then be able to add a new sheet to the spreadsheet.
References:
