Hi,

I have a add-in in xla format, and a worksheet that use the function of the
add-in ( they are in seperate directory) . I distribute both the add-in and
worksheet to another host. When I add the addin at the new host and open the
worksheet, a message pop up asking if update links, I clicked yes. Then I
saw the original formula has changed from "myfun(a2)" to something like
"c:\addin\myaddin.xla!myfun(a2)", and because myaddin.xla copied to a
different directory, the worksheet dosen't work any more and all the cells
give the result "#NAME?"

Can anyone help me how to let the cells' formula do not use the absolute
path for add-ins but use whatever add-in that exists?

Thank you.
Leo

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

'---------------------






"Leo Lin" wrote:

Hi,

I have a add-in in xla format, and a worksheet that use the function
of the add-in ( they are in seperate directory) . I distribute both
the add-in and worksheet to another host. When I add the addin at the
new host and open the worksheet, a message pop up asking if update
links, I clicked yes. Then I saw the original formula has changed from
"myfun(a2)" to something like "c:\addin\myaddin.xla!myfun(a2)", and
because myaddin.xla copied to a different directory, the worksheet
dosen't work any more and all the cells give the result "#NAME?"

Can anyone help me how to let the cells' formula do not use the
absolute path for add-ins but use whatever add-in that exists?

Thank you.
Leo

This works great! Thank you very much.

"keepitcool" wrote in message
...
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

'---------------------






"Leo Lin" wrote:

Hi,

I have a add-in in xla format, and a worksheet that use the function
of the add-in ( they are in seperate directory) . I distribute both
the add-in and worksheet to another host. When I add the addin at the
new host and open the worksheet, a message pop up asking if update
links, I clicked yes. Then I saw the original formula has changed from
"myfun(a2)" to something like "c:\addin\myaddin.xla!myfun(a2)", and
because myaddin.xla copied to a different directory, the worksheet
dosen't work any more and all the cells give the result "#NAME?"

Can anyone help me how to let the cells' formula do not use the
absolute path for add-ins but use whatever add-in that exists?

Thank you.
Leo

Leo..

Good to hear!

But please tell me...

Did you ever work with application events in a class module?
Did you need all the explanation or could /would you have figured out my code
by yourself?

just curious :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Leo Lin" wrote:

This works great! Thank you very much.

"keepitcool" wrote in message
...

I suggest you put the workbook and addin into the same folder anywhere
you use it. Excel seems to cope with this OK.

Regards
BrianB
==============================================


"Leo Lin" wrote in message ...
Hi,

I have a add-in in xla format, and a worksheet that use the function of the
add-in ( they are in seperate directory) . I distribute both the add-in and
worksheet to another host. When I add the addin at the new host and open the
worksheet, a message pop up asking if update links, I clicked yes. Then I
saw the original formula has changed from "myfun(a2)" to something like
"c:\addin\myaddin.xla!myfun(a2)", and because myaddin.xla copied to a
different directory, the worksheet dosen't work any more and all the cells
give the result "#NAME?"

Can anyone help me how to let the cells' formula do not use the absolute
path for add-ins but use whatever add-in that exists?

Thank you.
Leo