Search string to replace sheet protection multiple .xml files.

Ken McQueston

I have an Excel file that has sheet protection on multiple (400+) excel worksheets in an Excel spreadsheet and I don’t know the password. In order edit the file content I have to remove the sheet protection. I need to remove the following text string from each file: <sheetProtection algorithmName="SHA-512" hashValue="*" saltValue="*" spinCount="100000" sheet="1" objects="1" scenarios="1"/>. Each file has basically the same text string except for the hash value (shown as a * above) and the salt value (shown as an “*” above). Is there a way to search for and replace the string in all 400+ all of the files?

PeterJones

@Ken-McQueston ,

Making sure that the Find-in-Files is set to Regular Expression, then you can FIND = <sheetProtection algorithmName="SHA-512" hashValue=".*?" saltValue=".*?" spinCount="100000" sheet="1" objects="1" scenarios="1"/> and replace with the empty string.

.*? in regular expression mode means “zero or more characters, but don’t be greedy”, which should do what you want in both the hashValue and saltValue fields

Whenever doing find-in-files search/replace, make sure you have a backup in case something goes wrong.

(Also note that by breaking sheet protection without the password, you might be violating a user license or privacy agreement; do not follow these instructions to get around legal restrictions; only use it in case you have a private spreadsheet where you forgot your own password, and you have 100% legal right to be breaking that copy protection.)

Ken McQueston

@PeterJones

That worked! Thanks for your help!

Ken