Path names to xla file in formula

[Guest]

I have defined several functions in a xla file. When Excel saves the workbook
the physical path to the xla file is prepended to any references to those
functions in the formulas.

I want to be able to publish the workbook to our Portal, and have everyone
be able to open it without having to update the links if their xla is not
stored in the same path as mine.

How can I get around this? Is there a way to tell Excel not to include the
physical path to the xla file, or to automatically resolve it to the correct
path when opening the workbook?

Carl

 

[Chip Pearson]

Unfortunately, there is no way to do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks. I had pretty much come to that conclusion.

Carl

 

[Thomas Ramel]

Grüezi Carl

Carl L schrieb am 04.02.2005

I have defined several functions in a xla file. When Excel saves the workbook
the physical path to the xla file is prepended to any references to those
functions in the formulas.

I want to be able to publish the workbook to our Portal, and have everyone
be able to open it without having to update the links if their xla is not
stored in the same path as mine.

How can I get around this? Is there a way to tell Excel not to include the
physical path to the xla file, or to automatically resolve it to the correct
path when opening the workbook?

Maybe the following code in Workbook_Open() could help:

xla = "Your_AddIn.xla" 'Filename of your AddIn
aLinks = Me.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
res = Right(aLinks(i), Len(aLinks(i)) - InStrRev(aLinks(i), "\"))
If res = xla Then
Me.ChangeLink aLinks(i), xla, xlExcelLinks
Exit For
End If
Next i
End If




Regards
Thomas Ramel

 

[keepITcool]

Chip,

I once wrote an addin that did it, cant find the code though..

Its principle:

an application event handler monitoring opening of workbooks.
for opened workbooks, check filelinks.
if filelinks include refs to myAddin.xla but with different path,
then change refs.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Chip Pearson wrote :

 

[Guest]

Sure, I could fix it by writing code. I was hoping that Excel had an option
which would cause it to only store the name of the XLA file without the path.
Then when the workbook was opened it would find a match in the registered
add-ins and resolve the path from that...

Thanks for the suggestions.

Carl