Copying excel worksheet to a different workbook

Question

I'm trying to copy a worksheet to a different workbook into the last worksheet of the target workbook.

My workbooks and worksheets are created like this:

public Microsoft.Office.Interop.Excel.Workbook xlWorkbookMatrix;
public Microsoft.Office.Interop.Excel._Worksheet xlWorksheetMatrix;

I tried using worksheet.copy:

xlWorksheetMatrix.Copy(Type.Missing, xlWorkbookEvaluation.Sheets[xlWorkbookEvaluation.Sheets.Count]);

and worksheet.UsedRange.Copy:

xlWorksheetMatrix.UsedRange.Copy(xlWorkbookEvaluation.Sheets[xlWorkbookEvaluation.Sheets.Count]);

With both different methods I always get an error.

For worksheet.Copy:

System.Runtime.InteropServices.COMException occured in System.Dynamic.dll The Copy-property of the worksheet object can't be assigned

For worksheet.UsedRange.Copy:

System.Runtime.InteropServices.COMException occured in System.Dynamic.dll The Copy-property of the Range object can't be assigned

Kartik Khamborkar Kartik Khamborkar · Accepted Answer · 2019-05-29T08:49:48

Having a template sheet you want to fill many times, Hope this helps :

public void test()
{

    Excel.Application excelApp;

    string fileTarget = "C:\target.xlsx";
    string fileTemplate = "C:\template.xlsx";
    excelApp = new Excel.Application();
    Excel.Workbook wbTemp, wbTarget;
    Excel.Worksheet sh;

    //Create target workbook
    wbTarget = excelApp.Workbooks.Open(fileTemplate);

    //Fill target workbook
    //Open the template sheet
    sh = wbTarget.Worksheets["TEMPLATE"];
    //Fill in some data
    sh.Cells[1, 1] = "HELLO WORLD!";
    //Rename sheet
    sh.Name = "1. SHEET";


    //Save file
    wbTarget.SaveAs(fileTarget);

    //Iterate through the rest of the files
    for (int i = 1; i < 3; i++)
    {
        //Open template file in temporary workbook
        wbTemp = excelApp.Workbooks.Open(fileTemplate);

        //Fill temporary workbook
        //Open the template sheet
        sh = wbTemp.Worksheets["TEMPLATE"];
        //Fill in some data
        sh.Cells[1, 1] = "HELLO WORLD! FOR THE " + i + ".TH TIME";
        //Rename sheet
        sh.Name = i + ". SHEET";

        //Copy sheet to target workbook
        sh.Copy(wbTarget.Worksheets[1]);
        //Close temporary workbook without saving
        wbTemp.Close(false);
    }

    //Close and save target workbook
    wbTarget.Close(true);
    //Kill excelapp
    excelApp.Quit();
}

Copying excel worksheet to a different workbook

1 Answers