Count worksheets in (active) workbook

Add-in Express™ Support Service
That's what is more important than anything else

Count worksheets in (active) workbook
If I use the Worksheets collection, it fires an exception 
Henri Pellemans


Guest


Hi Andrei,

I thought the following should not be to difficult. I want to know the number of worksheets in the active workbook. My code fires an exception. 


Private Sub testButton_OnClick(sender As System.Object,
                                   control As AddinExpress.MSO.IRibbonControl,
                                   pressed As System.Boolean) Handles testButton.OnClick
        'option strict is always on
        Dim wb As Excel.Workbook = TryCast(ExcelApp.ActiveWorkbook, Excel.Workbook)
        Dim wss As Excel.Worksheets = TryCast(wb.Worksheets, Excel.Worksheets)
        Dim message As String = "Until now nothing went wrong"
        MessageBox.Show(message)
        'the next line fires an exception
        Dim wsCount As Integer = wss.Count
        If Not IsNothing(wss) Then
            Marshal.ReleaseComObject(wss)
            wss = Nothing
        End If
        If Not IsNothing(wb) Then
            Marshal.ReleaseComObject(wb)
            wb = Nothing
        End If
    End Sub


I read https://www.add-in-express.com/forum/read.php?FID=5&TID=12470 where you use this code: 


Dim wSheets As Excel.Sheets = wkb.Worksheets


I can see the difference, but why is my code not working? As I always have "option strict on" your piece of code needs some extra conversion.

Thanks for any help.

Henri
Posted 16 Jul, 2016 08:50:24 Top
Andrei Smolin


Add-in Express team


Posts: 18818
 Joined: 2006-05-11
Hello Henri,

As you know, the Sheets collection the *Worksheets property* returns contains objects of the Worksheet type only; the Sheets collection the *Sheets property* returns may contain worksheets, chart sheets, macro sheets. You cannot cast the Sheets type to the *Worksheets type* and vice versa.

I cannot find a way in the Excel object model to get an object of the *Worksheets* type; all methods that deals with sheets (any type of sheets) return an object of the Sheets type.


Andrei Smolin
Add-in Express Team Leader
Posted 18 Jul, 2016 02:51:50 Top