CODEWITHSUNDEEP
c#excelvstoexcel-interopexcel-addins
RGS
RGS

Reputation: 5211

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);

Upvotes: 0

Views: 145

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

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.

Upvotes: 1

Related Questions