Sheet getting protected after saving

[billegge]

We are updating a column in a spreadsheet and after saving this spreadsheet the spreadsheet is protected. We have not set any properties for protection, only the updating of cell values.

[billegge]

Additional information on this issue:

Environment:

Excel file created with Excel 2007, saved as 97-2003 compatibility mode file. No protection on the worksheet. Worksheet opens in Excel in compatibility mode.

Problem detail:

If you open a file already created in Excel, read from it and then write to it, the file will be protected after write is called. This is not dependent on whether or not data is modified in the file. If you do not call read, but only call write, the file is not protected regardless of whether or not data was modified.

Workaround:

Set all SheetProtection options after doing the read, but before the write. i.e.

xlsfile.Sheets[0].SheetProtection:=[spEditObjects,spEditScenarios,
spEditCellFormatting,spEditColumnFormatting,spEditRowFormatting,
spInsertColumns,spInsertRows,spInsertHyperlinks,spDeleteColumns,
spDeleteRows,spSelectLockedCells,spSortCellRange,spEditAutoFileters,
spEditPivotTables,spSelectUnlockedCells];

This will prevent the worksheet from being written as protected. However, if any of the options are left out this will not work. In addition, it is only necessary to do this one time and one time only. Worksheets manipulated in this manner will handle protection correctly in the future once this procedure has been run one time. Obviously, using this all the time would negate any protections that were enabled on purpose. It is only useful for non-protected worksheets the first time it is accessed.

[billegge]

In XLSWriteII2.pas, function WriteToStream, we have the following code:

for i := 0 to FXLS.Sheets.Count - 1 do begin
if (FXLS.Sheets.SheetProtection <> DefaultSheetProtections) then
FXLS.Sheets._Int_Records.PROTECT := 1
else
FXLS.Sheets._Int_Records.PROTECT := 0;

For files created in Excel 2007, even in compatibility mode, the only options set, and these are set even in an unprotected worksheet, are: spEditObjects, spEditScenarios, spSelectLockedCells and spSelectUnlockedCells. These are compared to DefaultSheetProtections and the comparison fails as DefaultSheetProtections includes all 15 options. The sheet is then written as protected, even though it is not. This problem does not appear in a plain vanilla file created with Excel 2003, with no protection, as the routine in XLSReadII2, RREC_SHEETPROTECTION, is never called and FXLS.Sheets[FCurrSheet].SheetProtection is not set with the current sheets protection values. I do not know if the default options on a plain vanilla unprotected Excel 2003 worksheet are the same those in an Excel 2007 file in compatibility mode as there is no Header.RecID in the file equal to the const BIFFRECID_SHEETPROTECTION so RREC_SHEETPROTECTION (in XLSReadII2.pas) doesn’t get called and the sheet protections are never changed from the default, thereby resulting in an unprotected worksheet being written, as expected, for an Excel 2003 file.

[pnconrad]

I get an undeclared identifier error. when trying to make these changes.

xlsfile.Sheets[0].SheetProtection:=[spEditObjects,spEditScenarios,
spEditCellFormatting,spEditColumnFormatting,spEditRowFormatting,
spInsertColumns,spInsertRows,spInsertHyperlinks,spDeleteColumns,
spDeleteRows,spSelectLockedCells,spSortCellRange,spEditAutoFileters,
spEditPivotTables,spSelectUnlockedCells];

[billegge]

That post was in 2008 and you may have an updated version.

[pnconrad]

As it was in 2008 it is in 2010.
I have just installed the latest version of XLSReadWriteII v4, and all sheets that I save are protected.

[pnconrad]

Solved.
In addition to dragging the component onto the page, one needs to add sheetdata4 to he uses clause.

[Warstone]

Not realy. Still protected, but right now I'm using demo version (upgrade in progress). Can it be a reason?