Leo,
It's become an elaborate answer and I do hope you appreciate the
niceties of using an application object...
Part one:
When the addin loads it needs to check if any of the open workbooks
have links to it. (by filename). If those links exist but the path is
different => then change the link.
This is what the RelinkAll and Relink procedures do.
It the rest of this post gets too complicated for you:
just copy the Relink and Relinker procedures to a standard module, then
put the RelinkAll somewhere on a Commandbutton in a toolbar.
But if you want to do it the nice way.. please read on:
Part two:
Once the addin is loaded, and a user opens a workbook which contains
broken links to the addins formulas, the event handler updates the
broken links automatically. For this we need a classmodule.
Now create a new CLASSmodule and RENAME IT clsAppEvents
(in the properties window)
This class will hook into the application events. And it can then
monitor all workbooks opened during the excel session (while the addin
is loaded)
This is only the most limited (but a VERY powerful) way of using a
classmodule. I suggest you do some reading on it BEFORE asking questions
here.
Then compile the addin. (you may have to set your VBE options to break
in classmodules) It it compiles all right save it.
Now the only thing you have to do is initialize the class bt running the
workbook_open procedure. THE CODE ONLY WORKS IF THE CLASS IS
INITIALIZED!! (or close and reopen the addin)
Then open a workbook with broken links.. et voila!
The only problem is that the workbook_open EVENT fires AFTER the "Update
Links?" dialog. The user should just cancel out of that dialog, if it
only has links to the addin functions.
You could prevent the display of that dailog by setting the
links/options in the linked workbook. You could do this manually,
OR you could use vba (in a before.save event)
ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways.
Note this should be done to the workbook containing the links not the
addin.
pff....
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage:
http://members.chello.nl/keepitcool >
Here's the code for th addin:
'-Code for addin ThisWorkbook object module
Option Explicit
'Object var to hold a reference to the class.
Public oAppEvt As Object
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set oAppEvt = Nothing
End Sub
Private Sub Workbook_Open()
Set oAppEvt = New clsAppEvents
End Sub
'-----------------------------
'-Code for addin clsAppEvents class module
Option Explicit
Option Compare Text
Dim WithEvents xlApp As Application
Private Sub Class_Initialize()
RelinkAll
Set xlApp = Application
End Sub
Private Sub Class_Terminate()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
Relink Wb
End Sub
Public Sub RelinkAll()
Dim Wb As Workbook
For Each Wb In Workbooks
Call Relink(Wb)
Next
End Sub
Public Sub Relink(Optional ByVal Wb As Workbook)
Dim lk As Variant
If IsEmpty(Wb.LinkSources(xlLinkTypeExcelLinks)) Then Exit Sub
For Each lk In Wb.LinkSources(xlLinkTypeExcelLinks)
If lk Like "*" & ThisWorkbook.Name And lk <> ThisWorkbook.FullName
Then
Wb.ChangeLink lk, ThisWorkbook.FullName, xlLinkTypeExcelLinks
End If
Next
End Sub
'---------------------
