How to stop Excel storing the absolute path to an XLA?

Question

I have an XLA file that is to be deployed to a number of users in the organisation as an Excel add-in. My intention is to deploy it to a directory in the user's "documents and settings" folder in "Application Data\MyCompany". (In fact this is all working through a wrapper that copies the latest version of the XLA locally and installs it as an Excel add-in).

However, if a user creates a sheet that references a function defined in this XLA then Excel appears to store the absolute path of the XLA in the function call. Thus, if the user sends the sheet to a colleague Excel fails to resolve the function as their copy of the XLA resides at a different absolute path (as their username is part of the absolute path).

My belief up until now was the Excel "just coped" with this as long as the XLA was installed as an add-in but this does not appear to be the case.

Is it really the case that I need to enforce an identical absolute path for my add-in for all users? This is possible within a single organisation but I honestly can't believe this is true as it seriously impedes sharing of XLS files.

Thanks.

Answer 1

There's no good way to do this. I put my xla files on a network share rather than locally and install them via the UNC path. That only works for me because everyone has access to the share, which may not be the case for you. Here's some other alternatives

http://www.dailydoseofexcel.com/archives/2008/06/02/fixing-links-to-udfs-in-addins/

Answer 2

I simply remove the path with a sub like this one:

Sub RemoveXlaPath()
'
' Goal: delete the path reference to the add-in, i.e. everything before and including the '!'
' ='C:\Program Files (x86)\Microsoft Office\Office14\LIBRARY\populator.xlam'!famedata(...)
'
    Cells.Replace What:="'C:\*xla*'!", Replacement:="", _
                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
End Sub