Excel Add-in reference question

[N1GHTEYES]

How do I force Excel to save references to add-in functions without the path?

To clarify: I have a useful add-in which I've distributed to several of my colleagues. Some of them have access to a shared area and, in accordance with advice I've previously got from this site, they all use a version of the add-in which is stored in that common area. However, some of them do not have such access and they store the add-in on their own C: drives.

If I give somebody a workbook which references any of the add-in functions, it generally seems to save not just the reference to the function and the add-in, but also the whole path to the add-in. So when one of the users who cannot see the shared area opens the workbook, the cells containig the functions from the add-in do not work - even though they do have their own version of the add-in installed. If they look in the cells containing the functions, instead of seeing just the function name, they also see the add-in name and a complete path to it which is the path of the version to which I had a reference when the book was created.

Of course, they can easily find the spurious path and then do a search and replace to delete it. The workbook then seems to be happy to use the version of the add-in they have installed. In fact, I have even added a button to the add-in to do just this - when pressed, it looks for any cells in the entire workbook which reference the add-in and, if there is a path associated with it, it deletes it.

That's OK, but it would be better if I could simply persuade Excel not to save the explicit path reference in the first place.

Until now I have believed this was not possible - after much searching onm this forum and elsewhere - but now I know it is.

When testing the button I mentioned above, I copied a workbook, which I knew used my add-in functions, from one computer to another on a different network entirely. I then opened it, expecting to find lots of cells with the spurious path references so that I could test the stripping routine. BUT, much to my amazement, there were none! All of the add-in fucntions worked perfectly and there was no trace of any cells with the explicit path to the previous system's version of the add-in.

Clearly I have done something different when saving this workbook, but I can't for the life of me see what!

I checked in the workbook open event code to see if I had added a routine to actively search for and strip the fixed paths, but there is no such code.

OK, so I obviously have a memory which would shame a goldfish, but I would really love to know what on earth I've done here. I'd really appreciate any suggestions you guys can make.

Thanks,

Tony

 

[AnotherHiggins]

I know I've run into this before, but it's been a long time....

I'm pretty sure the trick is to go to a computer that doesn't have the addin available at all, then either enter or edit the formula so that it doesn't have a file path in it.

You'll get the #NAME? error - that's OK.

Save and distribute that workbook. The formula will work on any computer with or without network access - just so long as the addin is already open.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[N1GHTEYES]

Thanks John.

That sounds like a pretty roundabout route, and I don't think it can be how the workbook in question was created because I don't normally work on any PCs which do not have the addin installed.

However, I'll give it a go in the morning at work, if I get a chance before the course I'm supposed to be on tomorrow, and let you know how it goes.

Tony

 

[N1GHTEYES]

Update:

I tried the method suggested and it kind of works.

It certainly does save the addin function call without the explicit path (but only if you delete the whole of the function spec - including the addin name - otherwise it insists on reinstating the path even if that path does not exist on the second machine). When the saved workbook is then opened on a machine with the addin installed however, the cell displays the NAME error. Simply clicking on the cell and hitting enter solves this problem - i.e. the NAME error goes away and the function works properly, but if there are multiple cells, each one requires its own reset. Hitting F9 does not cause them all to be fixed.

So, I'm pretty sure this is not how I created the workbook which seems to transparently call any and all of the addin functions, regardless of path if it is opened on a machine with the addin installed.

That means there IS a method to enable a workbook to be saved such that it will use an addin's function cleanly without specifying a fixed path, but I still don't know what it is. This is really frustrating.

 

[N1GHTEYES]

I think I've cracked it if anyone is interested.

You need to add a reference to the add-in in the code area of the workbook. BUT that is not all.

Simply adding a reference does NOT seem to solve the problem. You also seem to need to make the workbook do some code execution during the opening event (perhaps to force it to use the version of the addin on the target machine) by adding some code (it does not seem to matter what - even just declaring a variable seems to be enough) in the Workbook_Open event.

Having done the above, and saved the workbook, you can then even delete the workbook open code if you want, because thereafter it seems to retain the path-independent reference to the addin.

Exactly why this works baffles me, but it does seem to.