none
VB Excel Addin Workbook open event - workbook.sheets cast error RRS feed

  • Question

  • I am trying to create my very first EXCEL ADDIN with a ribbon. Below is my code so far, but I am receiving a casting error. What is causing this error? And how do I solve it?

    Imports System.Diagnostics
    Imports Microsoft.Office.Tools.Ribbon
    
    Public Class EllimetalRibbon
    
        Private WithEvents Application As Excel.Application
        Private ConfigColumnID As Integer
        Private Sub EllimetalRibbon_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles     MyBase.Load
            Application = EmmExcelAddin.Globals.ThisAddIn.Application
        End Sub
    
        Sub Application_WorkbookOpen(ByVal wb As Excel.Workbook) Handles Application.WorkbookOpen
            Dim oSheet As Worksheet
    
            For Each oSheet In wb.Sheets '**<---- Error here**
                '..................
            Next
    
        End Sub
    
    End Class

    System.InvalidCastException: 'Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Tools.Excel.Worksheet'.


    • Edited by E_Jef Thursday, January 30, 2020 7:55 AM changed title
    Thursday, January 30, 2020 7:55 AM

All replies

  • Hello,

    Although this page doesn't explicitly indicates this, the Sheets collection contains objects of the type Object, not Worksheet. Replace

    Dim oSheet As Worksheet

    with

    Dim oSheet As Object

    and try. In the loop body, check if oSheet is Worksheet, cast it and use as required.


    Regards from Belarus (GMT + 3),

    Andrei Smolin
    Add-in Express Team Leader

    Please mark answers and useful posts to help other developers use the forums efficiently.

    Tuesday, February 4, 2020 1:26 PM