Pages in this article
-
Fix #Name! Errors
-
Detecting Workbook Opening
-
Processing Newly Opened Workbook
-
Handle Workbooks Opened From Explorer
Processing The Newly Opened Workbook
Once the add-in has detected that the user opened a new workbook some
action has to be taken.
First of all, we'll check all external links of the workbook to see
if any point to a file who's name resembles our add-in. After that (to
be on the safe side, this is normally not needed) we also check all
formulas which use our UDF(s) and update them so they point to our
add-in.
The code shown below is part of a "normal" module called
modProcessWBOpen:
Sub ProcessNewBookOpened(oBk As Workbook)
'-------------------------------------------------------------------------
' Procedure : ProcessNewBookOpened
' Company : JKP Application Development Services (c) 2005
' Author : Jan Karel Pieterse
' Created : 2-6-2008
' Purpose : When a new workbook is opened, this sub will be
run.
' Called from: clsAppEvents.App_Workbook_Open and
ThisWorkbook.Workbook_Open
'-------------------------------------------------------------------------
'Sometimes OBk is nothing?
If oBk Is Nothing Then Exit Sub
If oBk Is ThisWorkbook Then Exit Sub
If oBk.IsInplace Then Exit Sub
CheckAndFixLinks oBk
CountBooks
End Sub
Sub CheckAndFixLinks(oBook As Workbook)
'-------------------------------------------------------------------------
' Procedure : CheckAndFixLinks Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2008
' Author : Jan Karel Pieterse
' Created : 2-6-2008
' Purpose : Checks for links to addin and fixes them
'
if they are not pointing to proper location
'-------------------------------------------------------------------------
Dim vLink As Variant
Dim vLinks As Variant
'Get all links
vLinks = oBook.LinkSources(xlExcelLinks)
'Check if we have any links, if not, exit
If IsEmpty(vLinks) Then Exit Sub
For Each vLink In vLinks
If vLink Like "*" & ThisWorkbook.Name
Then
'We've found
a link to our add-in, redirect it to
'its current
location. Avoid prompts
Application.DisplayAlerts = False
oBook.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
Application.DisplayAlerts = True
End If
Next
On Error GoTo 0
End Sub
Check out the comments in the code to find out what is going on.
Well, that should be all there is to it, right? Not so. Sometimes
when one double clicks a file in Explorer, Excel has already opened that
file BEFORE your add-in is fully loaded and initialised.
Next: How to handle workbooks opened from Explorer.
