Minimize pain from relocating an XLA function library

[Jerry W. Lewis]

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]

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]

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]

I think it was in the .private newsgroups. (I don't recall any suggested code,
but you could contact Tushar to verify.)

But it would use an application event that just looks for any workbook that's
being opened:

Chip Pearson has some notes at:
http://www.cpearson.com/excel/AppEvent.htm

A short sample (which goes under thisWorkbook):

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "Hey you created a workbook named: " & Wb.Name
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Hey you opened a workbook named: " & Wb.Name
End Sub

 

[Peter T]

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]

I think I have the concept now, thanks. It looks do-able in principle, but
would require a considerable validation effort. On problem that immediately
surfaced is that Excel won't let you change that link on a protected sheet.
This hole keeps getting deeper and deeper ...

Jerry

 

[Peter T]

On problem that immediately

surfaced is that Excel won't let you change that link on a protected

sheet.

I find I can't update links if any sheet in the workbook is protected, even
one that does not contain any links.

Regards,
Peter T

I think I have the concept now, thanks. It looks do-able in principle, but
would require a considerable validation effort. On problem that immediately
surfaced is that Excel won't let you change that link on a protected sheet.
This hole keeps getting deeper and deeper ...

Jerry

 

[Dave Peterson]

Maybe it could become an instruction email that you could mail out.

Sometimes relying on the user isn't really too bad. (And just include your work
phone number, home phone number, cell phone number, fax number...<bg>).

I think I have the concept now, thanks. It looks do-able in principle, but
would require a considerable validation effort. On problem that immediately
surfaced is that Excel won't let you change that link on a protected sheet.
This hole keeps getting deeper and deeper ...

Jerry