Excel Interop: How to copy a worksheet to another workbook?

Question

I need to merge the worksheets of some workbooks into one new workbook. What I tried is this, but I am getting "Unable to get the Copy property of the Worksheet class". What am I doing wrong? Thank you!

foreach (var sourceFileName in sourceFileNames)
{
    var sourceWorkbook = GetWorkbook(sourceFileName);

    var sourceSheet = (Worksheet)sourceWorkbook.Worksheets[1];
    var lastSheetInDestinationWorkbook = (Worksheet)_destinationWorkbook.Worksheets[_destinationWorkbook.Worksheets.Count];
    sourceSheet.Copy(After: lastSheetInDestinationWorkbook);

    sourceWorkbook.Close();
}
((Worksheet)_workbook.Sheets[1]).Delete();
_workbook.Save();

Does this answer your question? C# - How to copy a single Excel worksheet from one workbook to another? — Mikael
My crystal ball says that GetWorkbook() creates another Application object. — Hans Passant
I'm not a big fan of psychic debugging solutions. You've got some editing to do to make the mishap obvious, post the solution as well and mark it as the answer. — Hans Passant

IngoB IngoB · Accepted Answer · 2019-11-07T13:46:58

My fault was to load the workbooks in different Application objects. Using the same Application object solved the problem.

Thanks @HansPassant to point me there.

Excel Interop: How to copy a worksheet to another workbook?

1 Answers