save excel worksheet in shared not protected read only mode from C#

Question

I am saving a worksheet created in C# using Workbook.SaveAs method. The XlSaveAsAccessMode argument is giving me issues.

xlShared saves worksheet in protected mode so users cannot play around with charts and examine them carefully. It basically shows as a dead chart which cannot be referenced to data.

xlExclusive allows the user saving the book unprotected access but not others. The other users still see a dead chart.

I need to save the worksheet in a mode that allows everyone to view and examine it and be able to play around with charts, connect the series with data points, etc. But at the same time, they should not be able to save their changes to overwrite the file. How do I achieve this using C# COM Excel API?

References -

http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(v=vs.80).aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlsaveasaccessmode.aspx

Answer 1

First save your file using a password against modifications - you can do this in the Save As dialog (under the Tools dropdown on the bottom of the dialog) as you save out your file. Also check the Read-only recommended checkbox to force read-only mode on the file.

When users open the file, Excel will ask for the write password but users who don't have that password will be forced to go to read-only mode. In read-only mdoe, users can make any changes to the file but they can't save over the original file (regardless of the read-only attribute in the file system) - they can only save a new copy of the file under a new name (or the same name in a different folder).

To programmatically save your workbook with these properties, you can do something like:

oWorkbook.SaveAs ( oWorkbook.Path + @"\Workbook.xls",
    Excel.XlFileFormat.xlWorkbookDefault,
    missing,
    "WritePassword",  // password against modification
    true,             // read-only recommended
    false,
    Excel.XlSaveAsAccessMode.xlNoChange,
    missing,
    missing,
    missing,
    missing,
    missing );

You can also handle the WorkBookBeforeSave event and update the workbook with tracking information to identify copies of your original workbook. This would help with copies saved under the original file name in a different folder. (I'm not familiar with this particular event but you may even be able to cancel the save operation when handling it.)

Unless you fully control the file (which is near impossible using only Excel) users will be able to make copies of your file but the above settings at least force them to save new copies while the original is intact. Does this solve your problem?