Loop through Excel worksheets in Addin initalize

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

Loop through Excel worksheets in Addin initalize
 
Jens Ronnqvist


Guest


Hi,

I want to search the collection of Excel worksheets when the AddIn initializes in order to add some ADXRibbonButtons
to an ADXRibbonMenu. However, when I put 


For Each ws As Excel.Worksheet In AddinModule.CurrentInstance.ExcelApp.Worksheets


in the 


Private Sub AddinModule_AddinInitialize(sender As Object, e As EventArgs) Handles MyBase.AddinInitialize


it crashes. Where should I put such code that scans the worksheet collection when the AddIn initializses?

Thank you.
Posted 20 Jun, 2014 10:43:10 Top
Jens Ronnqvist


Guest


Hi,

The problem is solved. I placed the loop 


For Each ws As Excel.Worksheet In AddinModule.CurrentInstance.ExcelApp.Worksheets


inside my OnCreate method for the ADXRibbonMenu.
Posted 20 Jun, 2014 16:26:48 Top
Andrei Smolin


Add-in Express team


Posts: 18833
 Joined: 2006-05-11
Jens,

Please note that using For Each (VB.NET) and foreach (C#) on a COM collection is not recommended because the iterator created behind the scene doesn't release COM objects. Use a For loop instead.


Andrei Smolin
Add-in Express Team Leader
Posted 23 Jun, 2014 02:03:24 Top
Jens Ronnqvist


Guest


Hi Andrei,

You mean like this? 

Dim i As Integer
For i = 0 To AddinModule.CurrentInstance.ExcelApp.Worksheets.Count - 1
    Dim ws As Excel.Worksheet = AddinModule.CurrentInstance.ExcelApp.Worksheets(i)
Next


What do you mean by not releasing COM objects?
In a For Each loop, the variable 
ws
I used becomes a reference to a COM-object (an Excel worksheet)? I don't want to destroy the Excel worksheet after the function is done, won't the variable 
ws
simply go out of scope (actually out of scope outside the For Each loop) and the Garbage Collector deals with any unused variables?

Thank you.
Posted 23 Jun, 2014 03:02:16 Top
Andrei Smolin


Add-in Express team


Posts: 18833
 Joined: 2006-05-11
Jens, 

        Dim wkb As Excel._Workbook = ExcelApp.ActiveWorkbook()
        If wkb IsNot Nothing Then
            Dim wSheets As Excel.Sheets = wkb.Worksheets
            For iSheet As Integer = 1 To wSheets.Count
                Dim wks As Excel._Worksheet = CType(wSheets.Item(iSheet), Excel._Worksheet)                
                ' do something e.g. MessageBox.Show(wks.Name)
                Marshal.ReleaseComObject(wks) : wks = Nothing
            Next
            Marshal.ReleaseComObject(wSheets) : wSheets = Nothing
            Marshal.ReleaseComObject(wkb) : wkb = Nothing
        End If
    End Sub


As to the Garbage Collector, it should run some time later. This may be too late for a given COM object; this depends on the implementation and actual scenario. Releasing COM objects is what Office expects from you as Office was originally designed for being customized using COM the main rule of which is "Release every COM object created in your code as soon as you don't need it".


Andrei Smolin
Add-in Express Team Leader
Posted 23 Jun, 2014 04:09:46 Top
Jens Ronnqvist


Guest


Thank you, I will use your example.
Posted 23 Jun, 2014 07:11:19 Top