Hi,

I had a call about being able to unprotect the original tab of the the XLS files generated by APEX's Export to Excel feature. When attempting this, Excel requests a password. Is there a password being used to protect the sheet?

Thanks,
John Andreasen

Hi John,
As far as I know, it's possible to protect and define password using the CSV2XL directives , but Export from APEX is based on the CSV2XL.ini file and I don't know any option regarding protection/password, anyway, check the ini file, maybe you find there something.

PRTXLS sets the 'protect' attribute on the original sheet, but it doesn't set any password. Excel is a bit confusing on this point, but it should just let you unprotect it. (The only point of the protection in this case would be to avoid accidental changes.)

If you like, we could implement password protection via a new setting in the PRTXLS.INI file as Jorge suggests.

Hi Jorge and Jack,

Thanks for the replies. It does appear that the user can copy the data from the protected sheet and paste into a new unprotected one. So, that is certainly a workaround for not being able to unprotect the original one. Unless Jorge or someone else has an additional thought, I am not sure it is worth spending time on implementing.

Hi John, in fact you can crack the password protection, but the way to do it is far complicated than copy/paste the content.

That's very interesting Jorge. I may have to read up on that.

I wonder if your version of PRTXLS is old enough that it had a password in that function that has since been removed? One way to check that would be to generate a (preferably very small) test report, set debug tags +axl and then run it it through PRTXLS. (You'll have to do this via the PRINT command rather than APEX.) That should generate a LOT of trace messages, but you can search for "SetProtect". If there is a password, it will trace as "<password>" instead of "". That won't tell you want the password is but will confirm that it's not blank.

If the traces don't pan out, you may want to consider getting a newer version of PRTXLS (you can use UPDCUR from the BAS: directory). Latest is 2.1(218).

It's very simple to delete the protection, backup the original XLSX and change the extension to ZIP, inside of the zip find the sheet1.xml which is inside of folder .\xl\models open it in notepad and search for the tag "protection", remove everything regarding that tag and save the file outside the ZIP file. This done, replace the sheet1.xml inside the ZIP with the changed one and rename it back to XLSX.
It's supposed that protection has been removed.
Maybe you can even be able to decode the password with the hash and salt, but that I never needed, I'm just guessing.
You can find several videos on internet explaining this technique.

I checked the PRTXLS version, and it was 2.1(217). After updating to 2.1(218), the protection can be removed in Excel without a password prompt. So, I am afraid it might have just been a case of out of date software
Thank you both for your help. At any rate, it is interesting to know how to manually remove the protection.