CODEWITHSUNDEEP
vbavisual-studioexcelvsto
Adjit
Adjit

Reputation: 10305

Unable to cast COM object of type 'Excel.WorksheetClass' for VSTO add-in

I have tried a few things and am still getting this error. I am creating an excel VSTO using VBA and Visual Studio.

The way I have this setup is there is a button in the ribbon and that the user can click and it will go through and unlist all tables. I guess my issue lies with actually hooking onto the current active instance of Excel to loop through the worksheets.

An exception of type 'System.InvalidCastException' occurred in ConvertAllTablesToRange.dll but was not handled in user code

Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Tools.Excel.Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{297DC8D9-EABD-45A1-BDEF-68AB67E5C3C3}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Here's what I've tried:

Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click

    Dim wks As Worksheet, objList As ListObject

    For Each wks In Globals.ThisAddIn.Application.ActiveWorkbook  ' ERROR LINE

        For Each objList In wks.ListObjects
            objList.Unlist()
        Next objList

    Next wks
End Sub

and

Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
    Dim wks As Worksheet, objList As ListObject
    Dim exApp As Excel.Application

    exApp = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets

    For Each wks In exApp.Worksheets
        For Each objList In wks.ListObjects
            objList.Unlist()
        Next objList
    Next wks
End Sub

Both of which throw the same error

Upvotes: 0

Views: 1575

Answers (3)

Adjit
Adjit

Reputation: 10305

It turns out that there are 3 typecasting errors.

1- the wks variable needs to be set to Excel.Worksheet since Worksheet is Microsoft.Office.Tools.Execl.Worksheet while the type I need is Microsoft.Office.Interop.Excel.Sheet

2- Same goes for the objList variable. Needs to be Excel.ListObject instead of ListObject

3- I need to search the collection Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets

That clears up all of the errors and allows the code to function without fail.

Upvotes: 2

barrowc
barrowc

Reputation: 10689

In plain vanilla VBA which you can adapt to your requirements:

References to a Worksheet come from the Worksheets collection so code block #1 should have:

For Each wks In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets

In code block #2, assigning a Worksheets collection to an Application object makes no sense - two different, incompatible types. Try this instead:

Dim exApp As Excel.Application

' VBA requires Set for assigning any object type - remove
' the Set keyword if not needed in your approach
Set exApp = Globals.ThisAddIn.Application

For Each wks In exApp.ActiveWorkbook.Worksheets

Upvotes: 1

Malick
Malick

Reputation: 6742

You need to check if there is a worksheet (isnot nothing)

Dim NativeWorkbook As Microsoft.Office.Interop.Excel.Workbook =
Globals.ThisAddIn.Application.ActiveWorkbook
If NativeWorkbook IsNot Nothing Then
Dim vstoWorkbook As Microsoft.Office.Tools.Excel.Workbook =
    Globals.Factory.GetVstoObject(NativeWorkbook)
End If

see more information : here msdn

EDIT : here a working example but in C# , not in VB.NET, I think you can convert it because I'm not very efficient in VB.NET :

Microsoft.Office.Interop.Excel.Application XL =   Globals.ThisAddIn.Application;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = default(Microsoft.Office.Interop.Excel.Workbook);
            xlWorkBook = XL.ActiveWorkbook;
            foreach (Microsoft.Office.Interop.Excel.Worksheet xs_loopVariable in xlWorkBook.Sheets)
            {
                    foreach (Microsoft.Office.Interop.Excel.ListObject objList in xs_loopVariable.ListObjects)
                    {
                     objList.Unlist();
                    }
            }

Upvotes: 0

Related Questions