Error while using PasteSpecial for Excel in C#.

Question

0.00/5 (No votes)

See more:

Using following set of code to paste the values copied, But keep getting error messages..

xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Excel.Range R1 = (Excel.Range)xlWorkSheet1.Cells[1, 1];

R1.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,false,false);

Error :

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: PasteSpecial method of Range class failed

I tried to add R1.Select () , but it still failed..

Any help much appreciated

Posted 8-Jul-12 19:53pm

VaTN

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jim Jos · Answer 1 · 2012-07-08T21:12:00

Solution 1

Have you copied something on the clipboard before pasting.. You need to do a cut or copy.. The following code demonstrates that..

C#

Excel.Range R1 = (Excel.Range)oSheet.Cells[11, 11];
R1.Copy(Type.Missing);

Excel.Range R2 = (Excel.Range)oSheet.Cells[15, 15];
R2.PasteSpecial(Excel.XlPasteType.xlPasteValues,    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

The new code after your comments

C#

Excel.Range FilterRange = xlWorkSheet.get_Range("A1", "X6100");
       FilterRange.AutoFilter(6, "Project Leader", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
       Excel.Range splRange1 = FilterRange.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible, Type.Missing);
       splRange1.Copy(Type.Missing);
       xlWorkSheet = (Excel.Worksheet) oWB.Worksheets.get_Item(2);
       Excel.Range R1 = (Excel.Range)xlWorkSheet.Cells[1, 1];
       R1.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

Posted 8-Jul-12 21:12pm

Jim Jos

Updated 9-Jul-12 4:12am

v2

Comments

VaTN 9-Jul-12 9:42am

Yes I did copy before pasting .. Here the entire code which has copy part as well. Still have the issue explained earlier

FilterRange = xlWorkSheet.get_Range("A1", "BF800000");

FilterRange.AutoFilter(8,strValue , Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

Excel.Range splRange1 = FilterRange.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible,Type.Missing);

splRange1.Copy (Type.Missing);

xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Excel.Range R1 = (Excel.Range)xlWorkSheet1.Cells[1, 1];

R1.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,false,false);

Jim Jos 9-Jul-12 9:50am

What is BF800000? ARe you trying to select the entire sheet ?

VaTN 9-Jul-12 9:55am

Trying to filter the spreadsheet and then copy only filtered values

Jim Jos 9-Jul-12 10:02am

OK.. you are trying to get the first worksheet is it the same worksheet you set the autofilter? If you are trying to copy and paste from the same worksheet it ll not work as it has filter on..

VaTN 9-Jul-12 10:36am

Yes it was my bad that i was trying to paste in the same worksheet once I modify to paste in different worksheet it worked.

Thanks a lot for your help