Excel WB opens but then all COM errors

[FunkMonkey]

After I have the Excel WB open I am unable to perform a ws - xlApp.Worksheet call. I have xlApp as a global inside of my ClsExcel to ensure I'm working with the same Excel WB through the entire session. I have ran the xlApp through the Watch window and it is valid, but just won't create a Worksheet/Workbook. I also created an xl as new Excel.Application in the function and used xl.worksheet to see if the error would remain, and it did.

Public Class ClsExcel

    Private m_bDebugRun As Boolean

    Private m_szWBPath As String
    Private xlApp As New Excel.Application

This is one of the spots that causes a COM error shown below.

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

I'm just not sure what the COM message is telling me other than the interface is not supported. I don't know why as I was able to open the WB with a wb = xlApp.Workbooks call then WB Open to get the Excl WB active, but not any operation on the WB after that.

I don't see why the following code, which is just a minor change from WB to WS, is failing with this error message.

    Public Function LoadTab(ByVal strTab As String) As Range

        Dim bStatus As Boolean
        'Dim xl As New Excel.Application
        Dim ws As Worksheet

        Try


            ws = xlApp.Worksheets

            If (GetIsDebug()) Then
                Debug.Print("GetExcel [LoadTab Enter]")
            End If

            bStatus = True

            SelectExcelSheet(strTab)
            'Get the items in the tab
            LoadTab = ws.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).CurrentRegion

            If (GetIsDebug()) Then
                Debug.Print("GetExcel [LoadTab Exit]")
            End If

        Catch ex As Exception
            Dim szErr As String
            szErr = "Error occured for the following reason [" & ex.Message & "]"
            If (GetIsDebug()) Then
                Debug.Print(szErr)
            End If
            bStatus = False
        End Try

    End Function

Any inisight you can provide would be greatly appreciated.

[jmcilhinney]

I have moved this thread to the Office Development forum, as it is likely to be Office-specific solution. I don't really do Office develo0pment but, when I searched for this error message I see talk about mismatched versions and COM registration errors. Sounds like ensuring the Excel reference in your project matches the version of Office installed is one step and possibly repairing your Office installation is another. I'm sure you've already searched the web for that error message though, so you've seen all the same information that I just did.

[FunkMonkey]

I have moved this thread to the Office Development forum, as it is likely to be Office-specific solution. I don't really do Office develo0pment but, when I searched for this error message I see talk about mismatched versions and COM registration errors. Sounds like ensuring the Excel reference in your project matches the version of Office installed is one step and possibly repairing your Office installation is another. I'm sure you've already searched the web for that error message though, so you've seen all the same information that I just did.

Thanks jmc

[FunkMonkey]

I have moved this thread to the Office Development forum, as it is likely to be Office-specific solution. I don't really do Office develo0pment but, when I searched for this error message I see talk about mismatched versions and COM registration errors. Sounds like ensuring the Excel reference in your project matches the version of Office installed is one step and possibly repairing your Office installation is another. I'm sure you've already searched the web for that error message though, so you've seen all the same information that I just did.

My understanding after further research is that for Office 365 that Interop 16 is the one to use. I have 16 selected, the only one there, for 365 so is there another issue or am I reading the article incorrectly?

Anyone have any advise?

[squatman]

This looks like a duplicate post of yours:
https://www.vbforums.com/showthread....else&p=5675036

More code would be useful, as I replied in the other thread you can't assign Excel.Worksheets to Excel.Worksheet.

Dim ws As Worksheet
 
        Try
 
 
            ws = xlApp.Worksheets

Make sure you have added the Excel COM reference to your project and set up a simple test like this:

Imports Excel = Microsoft.Office.Interop.Excel
 
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlWs As Excel.Worksheet
 
        xlWb = xlApp.Workbooks.Open("C:\path\to\your\file.xlsx")
        xlWs = xlWb.Worksheets("Sheet1")
 
        xlWs.Range("A1").Value = "test"
        xlWb.Save()
        xlWb.Close()
        xlApp.Quit()
 
        MessageBox.Show("Excel file updated successfully!")
    End Sub
End Class