Is it possible to set
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
via the API?
Hi,
First of all sorry for the delay, I needed to investigate a bug that I detected when answering before I could write back.
Yes, it is possible and APIMate should tell you how to do it. Just create a file with the protection you want in Excel, then open the file in APIMate and it will tell you the code. For this particular case, it is:
SheetProtectionOptions := TSheetProtectionOptions.Create(TProtectionType.All);
xls.Protection.SetSheetProtection('', SheetProtectionOptions);
But there are 3 things to notice:
1)Contents, DrawingObjects, and Scenarios work in the reverse as all the other properties. In those 3 "true" means "protected" while in the others "true" means "user can modify this" which translates to "not protected". This is similar as in Excel with OLE Automation.
2)If saving as xls files, then FlexCel is currently reversing the meaning of DrawingObjects and Scenarios. It actually saves the values 2 times for different Excel versions (as always in Excel), but it saves the correct values for Excel < Excel XP and the reversed values for Excel XP and newer. So Excel 97 will open those files fine, but Excel XP or newer will have the values reversed.
We have already fixed this issue internally, so if you are saving to xls send me an email to adrian@tmssoftware.com with your registration email/code and I will send you the fixed version.
You can keep using the current version if you are saving as xlsx only.
3)APIMate is currently suggesting to use a line like:
SheetProtectionOptions := TSheetProtectionOptions.Create(true);
While this works, it makes more sense to use something like:
SheetProtectionOptions := TSheetProtectionOptions.Create(TProtectionType.All);
As the second variant will revert "Contents" and "DrawingObjects" so they are protected too.
Again, we have changed APIMate in our internal version.