Search string to replace sheet protection multiple .xml files.
-
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? -
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 fieldsWhenever 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.)
-
