Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel AddIns - changing locations
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel AddIns - changing locations
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 the HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Options -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] EA wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel AddIns - changing locations
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 "EA" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine information from two pages, two changing locations. | Excel Worksheet Functions | |||
Changing the order the addins load | Excel Discussion (Misc queries) | |||
Changing cell locations with formula? | Excel Discussion (Misc queries) | |||
Changing Formula Locations | Excel Discussion (Misc queries) | |||
Update multiple locations when changing a value on one worksheet | Excel Programming |