Path names to xla file in formula
 

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

Path names to xla file in formula
 

Unfortunately, there is no way to do this.


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


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

Path names to xla file in formula
 

Thanks. I had pretty much come to that conclusion.

Carl

"Chip Pearson" wrote:

Unfortunately, there is no way to do this.


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


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

Path names to xla file in formula
 

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

--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

Path names to xla file in formula
 

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 :

Unfortunately, there is no way to do this.

Path names to xla file in formula
 

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

"Thomas Ramel" wrote:

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

--
- MVP for Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]