CODEWITHSUNDEEP
c#.netexcelinteropms-office
Stacker
Stacker

Reputation: 8237

How can I edit or unprotect a single cell in a protected Excel sheet from C#?

I'm trying to manipulate an excel file with C# by using Microsoft.Office.Interop.Excel. The workbook I'm trying to manipulate has shared protection between users.

I have the file password and I have the password of the cells which I want to access and edit.

If I try to do that from Excel, I do the following:

I want to do this from a C# application.

My current code is as following:

xl.Application excelApp = new xl.Application();
excelApp.Visible = true;
xl.Workbook newworkbook =
    excelApp.Workbooks.Open(@"C:\1.xls", 0, false, 5, "password", "", false,
                            xl.XlPlatform.xlWindows, "", true, false, 0, true,
                            false, false);
xl.Sheets excelSheets = newworkbook.Worksheets;
xl.Worksheet excelWorksheet = (xl.Worksheet)excelSheets.get_Item("Sign On_Off");
excelWorksheet.Select(true);
xl.Range myrange = excelWorksheet.get_Range("b16", "b16");
myrange.Value2 = "testing";

The last line gives me this error message:

The cell or chart that you are trying to change is protected and therefore read-only.

I can't unprotect the whole sheet cause that would mean I will have the file opened exclusively for me only and other users can't save changes.

So my question: Is is there is way I can unprotect a cell only?

I imagine something like:

myrange.unprotect("pw");

Upvotes: 2

Views: 7301

Answers (2)

Mathias
Mathias

Reputation: 15391

I haven't had time to try it, but this paragraph looks like what you are looking for. It seems that the VBA equivalent object you are looking for is ActiveSheet.Protection.AllowEditRanges, which stores information about the areas that can be edited by users in a sheet.
One thing you might want to look into as well is the UserInterfaceOnly option on ActiveSheet.Protect. You can protect a range with a password for users, but access it without password through macros.

Upvotes: 1

Rubens Farias
Rubens Farias

Reputation: 57976

Protect is just available to Workbook and Worksheet classes.

Alternatively, you can try to work with Worksheet.Protection Property:

ActiveSheet.Protection.AllowEditRanges.Add _
    Title:="Range123", Range:=Range("K4:L10"), Password:="123"

Upvotes: 1

Related Questions