Copy Worksheet Excel VSTO C#

Question

I'm trying to copy a worksheet from an existing Excel file to my current workbook. I have an Excel Add-in with the following code:

        string wsName = "template." + ((System.Windows.Forms.Button)sender).Tag + "." + ((System.Windows.Forms.Button)sender).Text;

        Microsoft.Office.Interop.Excel.Application x = new Microsoft.Office.Interop.Excel.Application();
        x.Visible = false; x.ScreenUpdating = false;

        x.Workbooks.Open(Properties.Settings.Default.TemplatePath);

        try
        {
            foreach (Worksheet w in x.Worksheets)
                if (w.Name == wsName)
                    w.Copy(Type.Missing, Globals.ThisAddIn.Application.Workbooks[1].Worksheets[1]);
        }
        catch
        { }
        finally
        {
            x.DisplayAlerts = false; x.Workbooks.Close(); x.DisplayAlerts = true;       // close application with disabled alerts
            x.Quit(); x.Visible = true; x.ScreenUpdating = true;
            x = null;
        }

I open a second instance of excel in the background based on the template path. I find the worksheet w. This all works fine, however, what I try to copy the worksheet to the active workbook I can't make it to work. Current error is that the method Copy can't be found, however when I remove the argument it executes fine. So I assume my problem is in the Globals.ThisAddIn.Appllication.Workbooks[1]. Evaluating that argument does return the current workboook that is active in the AddIn.

Answer 1

The fact is that you are trying to use objects from different processes/threads:

 w.Copy(Type.Missing, Globals.ThisAddIn.Application.Workbooks[1].Worksheets[1]);

The w instance belog to a newly created Excel instance, but the parameter comes from the add-in instance.

If you need to copy the worksheet you need to open it in the existing Excel instance instead.