Excel AddIns - changing locations

[EA]

I have a situation which I do not understand.

I have just had my workstation upgraded from Office 2000 to Office 2003. I
have a new PC with a different 'build' on it, which I think means some files
are in different locations.

I use a series of Excel files which require functions from various 'in
house' Excel AddIns. Previously these AddIns were stored at:

C:\Program Files\Microsoft Office\Office\Library\

I am told that under the new build the AddIns are now located on my share
at:

H:\Settings\Application Data\Microsoft\AddIns\

This is correct because I can see they are there.

When I open my work files for the first time in Excel 2003, the files open
but all the functions from the AddIns return '#Name?'.

When I look at the formula in Excel 2003, I see that rather than just the
Function name [function name], I now have the AddIn path and the function
name:

C:\Program Files\Microsoft Office\Office\Library\[AddIn Name]![function
name]

I have checked the AddIn under Tools and the required AddIn is ticked.

My issue is how do I get my work file working again?

I have discovered that if I just manually remove the paths from the formula
then Excel calculates the function result correctly. However there are
thousands of function calls on each of about 25 worksheets - too many to do
manually.

I have thought about doing a search and replace to remove the path, but some
of the cell formulas, with the path added are more than 1024 characters so
search and replace will not work!

I have no idea why it calculates correctly because to me it does not know
about the new location of the AddIns and no file exists in the currently
specific (the old) location because I have checked.

Is there a way, the first time the file is opened, to get it to ask for the
location of the AddIn, in the same way Excel does when it cannot find a
linked file?

 

[Jake Marx]

Hi EA,

If you go to Tools | Add-Ins, select one of the missing add-ins, and click
Browse, Excel should tell you that the file is no longer where it expects.
You can browse to the file in the new location, click OK, and you should be
set. Just do this for each add-in (hopefully you don't have too many).

Alternatively, if you're comfortable with editing your registry settings,
you could go to the following key and modify the paths to your add-ins
there:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Peter T]

If I follow your addin is loading in it's new folder but you have links to
it's old path, try -

Dim sOldLink As String, sNewLink As String

sOldLink = "old-path\addinname.xla"
sNewLink = "new-path\addinname.xla"

ActiveWorkbook.ChangeLink sOldLink, sNewLink, xlLinkTypeExcelLinks

Alternatively you could programmatically search & replace. To overcome the
+1024 formulas, which are impossible to edit as strings, maybe temporarily
renaming sheets to a single letter will reduce sufficiently.

Regards,
Peter T