Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Fix Links to UDFs > Handle Workbooks Opened From Explorer
Deze pagina in het Nederlands

Fixing Links To UDFs in Addins

Handle Workbooks Opened From Explorer

The trick I used here is to schedule a macro when the add-in opens, which detects if any workbooks have to be processed. This is done using these lines in the Workbook_Open event of the add-in:

    modProcessWBOpen.TimesLooped = 0
    'Schedule macro to run after initialisation of Excel has fully been done.
    'Sometimes, the addin hasn't fully been initialised and the
    'workbook we want checked is opened BEFORE we have fully initialised the
    'addin.
    'This may happen when one double clicks a file in explorer
    Application.OnTime Now + TimeValue("00:00:03"), "CheckIfBookOpened"

I've set this up in such a way, that I count the number of times the routine called "CheckIfBookOpened" is run. After 20 times I suspend looking for new files, assuming there are none. Any new files opened will be handled by the class module.

Here is all the code that belongs to checkIfBookOpened (placed in the same module "modProcessWBOpen"). Note that I have added two module-level variables at the top of that module:

Option Explicit

'Counter to keep score of how many workbooks are open
Dim mlBookCount As Long

'Counter to check how many times we've looped
Private mlTimesLooped As Long

The remainder of the code is:

Sub CheckIfBookOpened()
'-------------------------------------------------------------------------
' Procedure : CheckIfBookOpened
' Company   : JKP Application Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 6-6-2008
' Purpose   : Checks if a new workbook has been opened (repeatedly until activeworkbook is not nothing)
'-------------------------------------------------------------------------
    'First, we check if the number of workbooks has changed
    If BookAdded Then
        If ActiveWorkbook Is Nothing Then
            mlBookCount = 0
            'Increment the loop counter
            TimesLooped = TimesLooped + 1
            'May be needed if Excel is opened from Internet explorer
            Application.Visible = True
            If TimesLooped < 20 Then
                'We've not yet done this 20 times, schedule another in 1 sec
                Application.OnTime Now + TimeValue("00:00:01"), "CheckIfBookOpened"
            Else
                'We've done this 20 times, do not schedule another
                'and reset the counter
                TimesLooped = 0
            End If
        Else
            ProcessNewBookOpened ActiveWorkbook
        End If
    End If
End Sub

Public Property Get TimesLooped() As Long
    TimesLooped = mlTimesLooped
End Property

Public Property Let TimesLooped(ByVal lTimesLooped As Long)
    mlTimesLooped = lTimesLooped
End Property

Function BookAdded() As Boolean
    If mlBookCount <> Workbooks.Count Then
        BookAdded = True
        CountBooks
    End If
End Function

Basically what happens is this:

Download the sample file

Conclusion

That's it. A bit convoluted, I agree. The other two options I noted at the start of this article may be simpler for your situation. Should you have an even simpler one, don't hesitate to leave a comment!


 


Comments

All comments about this page:


Comment by: hymced (11/22/2016 4:50:38 PM)

Hello

very interresting article !
just one thing : I can't get the point of the Workbook_BeforeClose and Workbook_Deactivate events on the ThisWorkbook VB component of the add-in ??

Thank you for your answer !

 


Comment by: Jan Karel Pieterse (11/22/2016 7:19:11 PM)

Hi hymced,

Those are there to avoind the add-in loosing the application events when:

- The user quits Excel (now the BeforeClose event fires, shutting down the application event handler)
- Excel asks to save changes and the user cancels the save and hence the quit of Excel

So the code schedules a macro that reinstates the event handler, but when the deactivate event triggers (just before the add-in closes) it unschedules that same event again to avoid the ontime macro forcing the add-in to be opened again.
Clear as mud?

 


Comment by: hymced (11/23/2016 2:29:30 PM)

Yes, after several reading, I do get it ! ^^

Here how I would put your explanation another way round :

These _BeforeClose and _Deactivate events are there to avoid the add-in loosing the application events when the user quits Excel (the BeforeClose event fires, shutting down the application event handler) but then cancels the save asked by Excel if there are changes (and hence the quit of Excel).

Indeed, the code schedules a macro that reinstates the event handler after the _BeforeClose event, but when the _Deactivate event triggers (just before the add-in closes) it unschedules that same event again to avoid the ontime macro forcing the add-in to be opened again.

Slightly different I agree :)

Anyway, thank you for your quick reply ;)

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].