Paste Values in Excel C#

Question

Hello I have been trying to use the PasteSpecial paste values on another sheet but every thing I've tried I keep receiving the same error.

PasteSpecial method of Range class failed

xl.Application xlApp = Globals.ThisWorkbook.Application;
xl.Workbook wb = xlApp.ActiveWorkbook;
xl.Worksheet data = wb.Sheets["Data"];
xl.Worksheet datasheet = wb.Sheets["DataSheet"];
xl.Worksheet pivot = wb.Sheets["Pivot"];
xl.Range dataRng = data.Range["A1:C" + 
      xlApp.WorksheetFunction.CountA(data.Range["A:A"])];
xl.Range pivotRng = pivot.Range["A1"];

addPivot ap = new addPivot();
ap.DataRange = dataRng;
ap.DestRng = pivotRng;
ap.PivotSheet = pivot;
ap.createPivot("FullName","Date");

pivot.PivotTables("PivotTable1").NullString = "0";
pivot.UsedRange.Copy();   

datasheet.Range["A2:GT999"].ClearContents();  

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone, 
                                   System.Type.Missing, System.Type.Missing);

datasheet.Range["2:2"].ClearContents();

I have Tried

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone)

and what you see above.

thanks!

Is this a typo or what means this: datasheet.Range["2:2"].ClearContents(); It should be something like: datasheet.Range["A2:A2"].ClearContents(); — Christine Ross
– Christine Ross, Commented Jun 2, 2015 at 11:45
no that is correct, that clears the contents of the entire row 2 — 3xGuy
– 3xGuy, Commented Jun 2, 2015 at 13:06

Byron Wall · Accepted Answer · 2015-06-02 15:05:28Z

Do the ClearContents before the Copy. Excel is removing your copied range (same as hitting ESC in the program) when you clear those cells out. Then you are trying to PasteSpecial but nothing is in the clipboard any longer.

//...code above

datasheet.Range["A2:GT999"].ClearContents();  

pivot.UsedRange.Copy();

datasheet.Range["A2"].PasteSpecial(xl.XlPasteType.xlPasteValues, 
                                   xl.XlPasteSpecialOperation.xlPasteSpecialOperationNone, 
                                   System.Type.Missing, System.Type.Missing);                                   
//code below...

If you are debugging this step-by-step, you would see the dashed border around the copied range disappear after the ClearContents call. Excel is a bit odd (but predictable) about when the copied range is forgotten. Editing cells anywhere in the Workbook is one sure way to make it forget.

Collectives™ on Stack Overflow

Paste Values in Excel C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related