Re: Minimize pain from relocating an XLA function library

I haven't seen Tushar's suggestion but I imagine it does pretty much as Dave
described, and makes sense. Dave did say it was for future reference, rather
than a fix for your current problem.

However you could similar in your new addin
In a withevents as application class in your addin look at each workbook as
it opens for links similar to your addin name, if any exist update them.

The downside is a potential delay as each wb opens, but typically not much.
Perhaps include a user option to turn automatic update on/off.

Regards,
Peter T

in a class module in your addin

Public WithEvents xlApp As Excel.Application

Private Sub xlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim vLink
vLink = Wb.LinkSources(xlLinkTypeExcelLinks)

If Not IsEmpty(vLink) Then
Erase vLink
For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks)
If vLink Like "PartofMyAddinName" Then
rest of code change link,
check it's not exactly MyAddinName
wb.ChangeLink vLink, MyAddinName, xlLinkTypeExcelLinks
etc
End Sub

In your addin's open event
set clApp = new ClassName
set clApp.xlApp = application

in a normal module

Public clApp as ClassName



"Jerry W. Lewis" <post_a_reply@xxxxxxxxxxxxx> wrote in message
news:F4BD5AFB-04FB-4315-851E-4B2D7F803BC5@xxxxxxxxxxxxxxxx
The same drive letter is what IS wants to avoid, as they expect Corporate
to
map that drive letter to a remote server whose contents we do not control.

I'm not sure that I understand what you believe to be Tushar's suggestion.
The xla would open when Excel is started; the concept of an event that
would
start then but remain active to monitor all future file opens is foreign
to
me -- can you elaborate? I found nothing when I searched for anything by
Tushar in microsoft.public.excel.* that contains the words "xla", "event",
and either "link" or "links".

Jerry

"Dave Peterson" wrote:

Just for the future...

I think it was Tushar Mehta who suggested that when the .xla file opens,
it
creates an application event that looks for workbooks opening. Then it
can try
to change the links itself.

(Not a pretty solution.)

Any chance that the network location was a mapped drive? Maybe just
using the
same drive letter (and path) would be sufficient. (Yeah, you already
thought of
that, but it's the only thing I could think of.)



Jerry W. Lewis wrote:

I wrote a library of utility functions in an XLA that have been in a
particular network location for about nine years. IS now wants to
move the
XLA to a different location, which will of course break all existing
uses,
since Excel embeds the path when a workbook is saved, instead of
checking for
the location from the registry's open add-in list (as it would with an
XLL).
I have no clue how many users and workbooks will be impacted (probably
hundreds of users and thousands of workbooks).

I can change the links on my workbooks on a case-by-case basis, but
this
approach would be problematic for less experienced users. Any
suggestions?

Jerry

--

Dave Peterson



.

Quantcast