ExcelSheet CopySepecial having problem in XlPasteType .

Question

Hi

 

I am trying to copy and paste values from an existing spreadsheet into a new spreadsheet and cannot figure out why it keeps blowing up. Any help would be appreciated. I am using the following code:

 

public Excel.Worksheet PasteSpecial(Excel.Application _XlApp, Excel.Worksheet _WorkSheet, string StartRangeFrom, string EndRangeFrom, string StartRangeTo, string EndRangeTo)

{

try

{

Excel.Range _XlRangeFrom = _WorkSheet.get_Range(StartRangeFrom, EndRangeFrom);

Excel.Range _XlRangeTo = _WorkSheet.get_Range(StartRangeTo, EndRangeTo);

_XlRangeFrom = _XlRangeFrom.EntireRow;

_XlApp.CutCopyMode = Excel.XlCutCopyMode.xlCopy;

_XlRangeFrom.Copy(Missing.Value);

//_XlApp.ScreenUpdating = false;

_XlRangeTo.PasteSpecial(Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);

return _WorkSheet;

}

catch (Exception Ex)

{

throw Ex;

}

}

 

 

when i edit

_XlRangeTo.PasteSpecial(Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);

to

_XlRangeTo.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);

 

it works Fine.But for PasteType.xlPasteAll,xlPasteValue gives me COM EXCEPTION:-- PasteSpecial method of Range class failed

 

Plese help me out of this issue.

 

Thanks & Regards

Dwipayan Das

Software associate Enginner

Answer 1

Hi Dwipayan,

 

When you specify the xlPasteType to be xlPasteAll, the ToRange should have the same size of the FromRange. Otherwise, it will fail.

And I do not know why you include this line:

Code Block

_XlRangeFrom = _XlRangeFrom.EntireRow;

 

 

This line will make RangeFrom to be several whole rows in the worksheet, which may resulted into the issue.

When I comment this line, and call your method like the following in my side. It works!

Code Block

PasteSpecial(excel, (Excel.Worksheet)excel.ActiveSheet, "A1", "A5", "C1", "C5");

 

 

 

Thanks

Ji

 

Answer 2

Thanks a lot for your replie.Now i have made changes as per your suggestions and its working fine.

 

Thanks & Regards

Dwipayan Das

Software Enginner

Visualbeam Technologies,India