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" wrote:

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





Jerry W. Lewis wrote:

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


--

Dave Peterson