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

Unfortunately, there is no way to do this.


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


"Carl L" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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]

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.

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]
>