CODEWITHSUNDEEP
.netexcelvb.netexcel-interop
Sylwester
Sylwester

Reputation: 33

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'

I'm writing a class that utilizes the Microsoft.Office.Interop.Excel assembly. It's a part of a "one stop shop" DLL library which will be utilized in a java solution (to limit the amount of interfacing on java side).

I am getting the following error:

Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208B1-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

This is thrown by the following code:

Public Class XL

    Public XL As Excel.Application = Nothing
    Public XLN As String
    Public WBS As Excel.Workbooks = Nothing
    Public WBSN() As String
    Public WB As Excel._Workbook = Nothing
    Public WBN As String
    Public WSS As Excel.Worksheets = Nothing
    Public WSSN() As String
    Public WS As Excel._Worksheet = Nothing
    Public WSN As String
    Public XLCelllValue As Object = Nothing

    Public Sub New()

        XL = New Excel.Application()
        XL.Visible = True

        WBS = XL.Workbooks
        WB = WBS.Add()

        WSS = WB.Worksheets '<this is the line that throws the exception
        WS = WSS(1)

    End Sub
End Class

I'm not sure what I'm doing wrong All properties are declared as public, Worksheets is a valid collection the WB property type is Excel._workbook and the WSS Property type is Excel.worksheets.

Any ideas what I'm missing?

Upvotes: 2

Views: 3995

Answers (2)

Bugs
Bugs

Reputation: 4489

Use the Sheets instance:

The Sheets collection can contain Chart or Worksheet objects. The Sheets collection is useful when you want to return sheets of any type. If you need to work with sheets of only one type, see the object topic for that sheet type.

With this in mind change the following declaration:

Public WSS As Excel.Worksheets = Nothing

To:

Public WSS As Excel.Sheets = Nothing

Additionally I've also noticed that you are using _Workbook and _Worksheet which do not have access to the DocEvents_Event members.

You should consider using Workbook which inherits from _Workbook and Worksheet which inherits from _Worksheet. Both Worksheet and Workbook inherit from DocEvents_Event which give you access to the following members:

enter image description here

This would only matter if you wanted to use the handlers but thought it was worth noting.

Lastly, on a smaller note you should turn Option Strict On. This will help your write better code and generate potential runtime errors at compile time. As it stands, this kind of code WS = WSS(1), with Option Strict On, will create the following compile error:

Option Strict On disallows implicit conversions from 'Object' to 'Microsoft.Office.Interop.Excel.Worksheet'.

Often the compiler will suggest a fix and in this case the fix will be:

WS = CType(WSS(1), Excel.Worksheet)

In your case this probably wouldn't create a runtime error however by having Option Strict On you can save yourself a lot of grief.

Upvotes: 3

Andrew Mortimer
Andrew Mortimer

Reputation: 2370

It is a type mixup.

WB.Worksheets returns a collection of Sheets

So you'll need

    Dim WSS As Excel.Sheets = Nothing

Upvotes: 0

Related Questions