COM Catches Excel workbook on startup

[Jan]

Hi

I am trying to write a COM Add-in that checks the Excel-workbook on startup (for instance when a user double clicks the workbook in the Windows Explorer or when the workbook is started from another application (parameter))

This works fine on five computers where Excel uses the same sequence of events
- Excel starts, workbook is loaded, Add in is started, workbook_activate is triggere
Because the add-in starts after the workbook is loaded, it can check the file (in the OnStartupComplete-event) and make the necessary changes

On one pc however, the order is different
- Excel starts, add-in is loaded, workbook is loade
Because of this different order, the add-in does not find a workbook. Besides that, the add-in does not 'see' the Workbook_activate events

I have two questions which I hope you can answer
- what determines the order? (the 6 pc's have the same windows/office version and it isn't the performance)
- is there another way to make modifications to the Excel file which is loaded on startup of Excel

Regards

Jan

 

[Stephen Bullen]

Hi Jan,

I am trying to write a COM Add-in that checks the Excel-workbook on startup (for instance when a user double clicks the

workbook in the Windows Explorer or when the workbook is started from another application (parameter)).

This works fine on five computers where Excel uses the same sequence of events:
- Excel starts, workbook is loaded, Add in is started, workbook_activate is triggered
Because the add-in starts after the workbook is loaded, it can check the file (in the OnStartupComplete-event) and make the necessary changes.

On one pc however, the order is different:
- Excel starts, add-in is loaded, workbook is loaded
Because of this different order, the add-in does not find a workbook. Besides that, the add-in does not 'see' the Workbook_activate events.

I have two questions which I hope you can answer:
- what determines the order? (the 6 pc's have the same windows/office version and it isn't the performance)
- is there another way to make modifications to the Excel file which is loaded on startup of Excel?

I don't know why it's in a different order, but I don't recall ever seeing anything that guarantees the order of events firing
either <g>.

One solution would be to hook the application's WorkbookOpen event to handle the case if there isn't a workbook open already.
Something like (untested):


Dim WithEvents oXL As Excel.Application
Dim mbProcessed As Boolean

Private Sub AddinInstance_OnConnection(ByVal Application As Object,...)

Set oXL = Application

End Sub

Private Sub AddinInstance_OnStartupComplete(custom() As Variant)

If oXL.Workbooks.Count > 0 Then
'Do your stuff
mbProcessed = True
End If

End Sub

Private Sub oXL_WorkbookOpen(ByVal Wb As Excel.Workbook)

If Not mbProcessed Then
'Do your stuff
mbProcessed = True
End If

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk