c# - (Excel interop)How can I copy one worksheet to another workbook in VSTO?

Question

I have searched a lot from the internet, but unfortunately, none of them worked. How to copy sheets from a workbook to another workbook ,Cutting and Pasting Cell Values in Excel using C#,Error: Pastespecial method of range class failed .

The thing is if I created the worksheet in the same workbook, the code works perfectly fine. But if I created a new workbook, then the Paste would not work.

   //copy function: copy to a new worksheet in the same workbook
   var copyFromRange = oldSheet.Range[
                           (Excel.Range)oldSheet.Cells[1, 1],
                           oldSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];
   copyFromRange.Copy(Type.Missing);
   var copyHeaderRange = newSheet.Range[(Excel.Range)newSheet.Cells[1, 1], newSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];
   copyHeaderRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);


   //Copy to a new workbook, won't work
   var excel = new Excel.Application();
   var newWorkbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
   var newSheet = (Excel.Worksheet)newWorkbook.Worksheets.Add();
   newSheet.Name = "sheetName";

   //copy function, same as above. But this won't work, PasteSpecial would throw null reference error.
   //If I changed the copyHeaderRange to below, then it will throw: PasteSpecial method of Range class failed.
   var copyHeaderRange = (Excel.Range)newSheet.Cells[1, 1];

   //If the copyRange is defined as below, it will throw null reference error while pastespecial
   var copyHeaderRange = newSheet.Range[(Excel.Range)newSheet.Cells[1, 1], newSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];

I have also tried open a new workbook instead of starting a new one, won't work . Tried activate the worksheet before copying, won't work either.

Any idea why this happens?

Possible duplicate of Error: Pastespecial method of range class failed — Chris
@Chris, I've checked this post before, unfortunately, none of them worked in my case. — Spencer
Replacing the paste with a line that's something like newSheet.Range["A1:J10"].Value = oldSheet.Range["A1:J10"].Value didn't fix it? Or do you need more than the values? — Chris

David Zemens David Zemens · Accepted Answer · 2018-06-12T21:11:09

If there are any merged cells in the copy range -- even if they do not span beyond that range's coordinate cells -- and you're creating a new instance of Excel Application class, I think this error is expected.

Rather than newing your excel instance, can you modify to use the same instance of Excel (untested):

var newWorkbook = oldSheet.Application.Workbooks.Add(System.Reflection.Missing.Value);
var newSheet = (Excel.Worksheet)newWorkbook.Worksheets.Add();
newSheet.Name = "sheetName";

c# - (Excel interop)How can I copy one worksheet to another workbook in VSTO?

1 Answers