C# - Excel Copy and Paste Formula in Protecte...

Hi,

I am working on a windows service, which will open a Excel file in In-Visible mode. Then it will add some data to some sheets and perform calculations using formulas in excel and get the result. This all working fine, but i am facing a problem regarding Copy & Paste formula in Hidden and Protected sheet and protected work book.

Here is my code so far:

//Copy the first cell which has formula

Excel.

Range copyRange = sheet.get_Range(FIRST_CELL, FIRST_CELL);

sheet.Unprotect(

"dsfdfdsfds");

copyRange.Select();

copyRange.Copy(

Type.Missing); \\EXCEPTION HERE

//Find last cell in this sheet

int lastRow = ((Excel.Range)sheet.Cells[7, 3]).get_End(Excel.XlDirection.xlDown).Row;

//Paste formula into all the "A" column cells

Excel.

Range pasteRange = sheet.get_Range(START_AT_CELL, "A" + lastRow.ToString());

pasteRange.PasteSpecial(Excel.

XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

In the above code, i getting exception at copyRange.Copy(Type.Missing);

Exception: Select Method of range class failed.

Please help.

Answers

Thursday, September 16, 2010 2:27 AM

Amy LiMSFT, Moderator

Hi RedRoseASP,

I've made the program in VS2010 and Excel2010. It's a Workbook Project. After running it, it won't throw
such an exception. So I'm not sure if I have missed something. Here is the code snippet. Please check to see if
it will solve the problem.

 private void Sheet1_Startup(object sender, System.EventArgs e)    {    Excel.Application thisApp=this.Application;    Excel.Workbook mybook = thisApp.Workbooks[1] as Excel.Workbook;    mybook.Protect();    Excel.Worksheet sheet=thisApp.Worksheets[1] as Excel.Worksheet;    sheet.Protect("aaa", this.ProtectDrawingObjects,    true, this.ProtectScenarios, this.ProtectionMode,    this.Protection.AllowFormattingCells,    this.Protection.AllowFormattingColumns,    this.Protection.AllowFormattingRows,    this.Protection.AllowInsertingColumns,    this.Protection.AllowInsertingRows,    this.Protection.AllowInsertingHyperlinks,    this.Protection.AllowDeletingColumns,    this.Protection.AllowDeletingRows,    this.Protection.AllowSorting,    this.Protection.AllowFiltering,    this.Protection.AllowUsingPivotTables);    Excel.Range copyRange = sheet.get_Range("D1","D1");    sheet.Unprotect("aaa");    copyRange.Select();    copyRange.Copy(System.Type.Missing);    int lastRow = ((Excel.Range)sheet.Cells[7, 3]).get_End(Excel.XlDirection.xlDown).Row;    string START_AT_CELL = "A1";    Excel.Range pasteRange = sheet.get_Range(START_AT_CELL, "A" + lastRow.ToString()); ;    pasteRange.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);    }

In the Sheet1 of Workbook I've set the value of B1 to 1 and C1 to 4. Add the SUM formula to D1. Then copy the
formula in D1 to all the A columns of the Sheet1. If I misunderstood you or any further questions, please feel free
to contact. Also here is a link about the issue of "Change in behavior from Office2007 RTM to SP2 when Sheet.Unprotect is called":
http://blogs.msdn.com/b/vsod/archive/2009/09/11/issue-change-in-behavior-from-office-2007-rtm-to-sp2-when-sheet-unprotect-is-called.aspx
Hope it will be helpful. Thank you!

Best Regards,
Amy Li

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Marked As Answer byAmy LiMSFT, Moderator7 hours 8 minutes ago

All Replies

Thursday, September 16, 2010 2:27 AM

Amy LiMSFT, Moderator

 private void Sheet1_Startup(object sender, System.EventArgs e)    {    Excel.Application thisApp=this.Application;    Excel.Workbook mybook = thisApp.Workbooks[1] as Excel.Workbook;    mybook.Protect();    Excel.Worksheet sheet=thisApp.Worksheets[1] as Excel.Worksheet;    sheet.Protect("aaa", this.ProtectDrawingObjects,    true, this.ProtectScenarios, this.ProtectionMode,    this.Protection.AllowFormattingCells,    this.Protection.AllowFormattingColumns,    this.Protection.AllowFormattingRows,    this.Protection.AllowInsertingColumns,    this.Protection.AllowInsertingRows,    this.Protection.AllowInsertingHyperlinks,    this.Protection.AllowDeletingColumns,    this.Protection.AllowDeletingRows,    this.Protection.AllowSorting,    this.Protection.AllowFiltering,    this.Protection.AllowUsingPivotTables);    Excel.Range copyRange = sheet.get_Range("D1","D1");    sheet.Unprotect("aaa");    copyRange.Select();    copyRange.Copy(System.Type.Missing);    int lastRow = ((Excel.Range)sheet.Cells[7, 3]).get_End(Excel.XlDirection.xlDown).Row;    string START_AT_CELL = "A1";    Excel.Range pasteRange = sheet.get_Range(START_AT_CELL, "A" + lastRow.ToString()); ;    pasteRange.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);    }

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。