CODEWITHSUNDEEP
c#excelexcel-interop
AC25
AC25

Reputation: 423

C# Excel casting

I have an existing project that creates an excel spreadsheet using the Microsoft.Office.Interop.Excel.WorkbookClass and I am trying to convert the workbook object to the Microsoft.Office.Tools.Excel.Workbook, but I am getting an exception thrown stating:

"Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.WorkbookClass' to class type 'Microsoft.Office.Tools.Excel.Workbook'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."

Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Tools.Excel.Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Tools.Excel.Worksheet xlNewSheet = null;

xlApp = new Interop.Application();
xlApp.Visible = true;
xlWorkbook = (Microsoft.Office.Tools.Excel.Workbook)xlApp.Workbooks.Add(Interop.XlWBATemplate.xlWBATWorksheet);
xlSheets = xlWorkbook.Sheets as Interop.Sheets;
xlNewSheet = (Microsoft.Office.Tools.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
xlNewSheet.Name = "SheetName1";

Is this possible if not, what other options can I take that since the excel sheet is already created using the interop class and

Upvotes: 2

Views: 3091

Answers (2)

F Snyman
F Snyman

Reputation: 509

If possible try to avoid using the Microsoft.Office.Tools assembly which is internal to Visual Studio Tools For Office.

I've amended your code as below:

    Microsoft.Office.Interop.Excel.Application xlApp = null;
    Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
    Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
    Microsoft.Office.Interop.Excel.Worksheet xlNewSheet = null;

    xlApp = new Microsoft.Office.Interop.Excel.Application();
    xlApp.Visible = true;
    xlWorkbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    xlSheets = xlWorkbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
    xlNewSheet = xlSheets.Add(xlSheets[1], System.Type.Missing, System.Type.Missing, System.Type.Missing);
    xlNewSheet.Name = "SheetName1";

Upvotes: 1

Seth Moore
Seth Moore

Reputation: 3545

It looks like to "convert" an interop workbook to a vsto workbook you just do this:

In application-level projects, you can create Microsoft.Office.Tools.Excel.Workbook objects programmatically by using the GetVstoObject method.

Microsoft.Office.Interop.Excel.Workbook nativeWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
if (nativeWorkbook != null)
{
    Microsoft.Office.Tools.Excel.Workbook vstoWorkbook = 
        Globals.Factory.GetVstoObject(nativeWorkbook);
}

Upvotes: 0

Related Questions