Search code examples
c#excelvstoexcel-interopexcel-addins

No Such Interface supported while copying worksheets from source Workbook to destination Workbook - Excel VSTO Addin

I am trying to copy the worksheets from input Excel file into the VSTO Excel workbook. I am getting "No such interface supported" while copying the sheets from the input Excel file. If I use same workbooks of Interop Excel type, then copy worksheets functionality is working fine. Please help me to resolve this issue.

    private Excel.Application _excel;
    private Excel.Workbook _wb;
    _excel = new Excel.Application();
    _wb = _excel.Workbooks.Open(strFileName);
    Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets["sheet1"];
    _wb.Worksheets.Copy(ws, Type.Missing);

I have also tried to use VSTO Excel Addin instance to load the source Workbook file. It's launching the source Workbook on top of VSTO Excel workbook. I have to process multiple Workbooks by opening the Excel files silently without launching the Workbooks.

    Excel.Application excel = Globals.ThisAddIn.Application;
    Excel.Workbook sourceWorkbook = excel.Workbooks.Open(FileNamePath);
    Excel.Worksheet xlWsSummary = sourceWorkbook.Worksheets[1];
    xlWsSummary.Visible = XlSheetVisibility.xlSheetVisible;
    xlWsSummary.Unprotect("12345");
    xlWsSummary.Activate();
    xlWsSummary.Copy(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1], Type.Missing);
Solution

  • First, there is no need to use the Globals namespace to access the Excel Application instance:

    Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets["sheet1"];
_wb.Worksheets.Copy(ws, Type.Missing);

    Second, if you develop a VSTO add-in there is no need to create a new Excel Application instance in the code:

    _excel = new Excel.Application();

    Instead, use the Application property of the ThisAddin class.

    Note, the Workbooks property returns a Workbooks collection that represents all the open workbooks. So, you just need to copy a worksheet from the opened workbook to the existing one.