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?](https://stackoverflow.com/questions/3808368/c-sharp-how-to-copy-a-single-excel-worksheet-from-one-workbook-to-another) — Mikael, Nov 06 '19 at 18:49
My crystal ball says that GetWorkbook() creates another Application object. — Hans Passant, Nov 06 '19 at 19:27
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, Nov 07 '19 at 12:08

score 2 · Accepted Answer · answered Nov 07 '19 at 13:46

2

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.

answered Nov 07 '19 at 13:46

IngoB

2,552
1
20
35

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

1 Answers1