Add-In path problem 1

[N1GHTEYES]

This may have been adressed before, but if so, I can't find it. The problem is this:

I've written an add-in which several of my colleagues use. I store my copy in the usual folder for addins on my C:\ drive. They store theirs the same way.

If I create a spreadsheet, which uses some of the functions from the add-in, and store it on a shared drive (or, for that matter give them a copy on a floppy and they copy it to their own drives), when they open it, it has saved the references to the add-in functions with the whole path\addin_name\function_name reference. So, although they have the add-in, the function in the cell is trying to use a function from an add-in on MY drive.

It is fairly easy to solve the problem, for the more excel-literate users. All they have to do is search and replace with "" for my path to the addin. That then leaves just the function name, and it gets called for their installed addin. I've even included some code which does that in the open event on some sheets, so they don't have to.

BUT - it still comes up with an anoying initial message, about updating links to another workbook (the addin) before the workbook open event fires.

Does anyone know how to avoid the whole problem by forcing Excel to save the reference to the add-in function using JUST the function name, avoiding any explicit path ref?

After all, if I use a function from, say the Analysis toolpack, I can give a copy to a colleague and it does not try to refer to the AT on my machione - it just works. How is a user-defined add-in different from the AT?

 

[firefytr]

Hi there,

I'd recommend storing the add-in, just one copy of it, on a shared drive. When the users install the add-in and it asks if they want to copy it to their local drive, have them click No. This will ensure they use the network file.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[N1GHTEYES]

Thanks Zack,

I work in a fairly strapped-down secure environment - not all users have access to common areas (at least, not ones where I have write priviledges). I can do it for some users (those in my dept), but I was not sure about doing this for three reasons.

1) XL seems to assume add-ins will be in a specific folder. I know you CAN browse to anywhere to get them, but I wasn't sure if there were any detrimental effects of not using the normal one.

2) The shared drive is a bit slow. If the add-in is loaded completely into RAM when XL opens, then that will only slow the startup slightly, so it won't be much of a problem. If, however, XL actually gets its add-in inputs from the stored file as it needs them, then that will be horribly slow. I'm assuming the former, but I'm not certain which actually happens.

3) Would there be any issues with more than one user simultaneously using the add-in?


The big problem though is with users in different depts, or even people who are not on the same network. So I come back to the original question. Is there a way of forcing XL to store the formulas in the cell without explicitly referencing the add-in path? As I said previously, it does that with the Analysis Toolpack functions - which come from an add-in - so why not mine?

 

[firefytr]

Regarding your #1 issue, I actually NEVER use the default folder. There is absolutely nothing wrong with using a different folder. The only problem is, can you remember where it is? I could never remember the entire path to the default folder. I did a lot of manual navigation to this type of area, so I just used C:\WINDOWS\ADDINS. It seemed so simple and spot on to me, I've been using it ever since. But to each their own.

When add-ins open, they are not opened into RAM, they are actually files which are opened, just hidden in the background. Once loaded, it is open and in the working environment. You would see speed issues, if over a slow network connection, in opening and saving. Basically anytime the file is accessed to its original location. *

Re #3: Nope.

* There is one thing you may want to consider here. Read it here:

http://www.tek-tips.com/viewthread.cfm?qid=595209

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[N1GHTEYES]

Thanks for that Zack.

The thread you cited seems to have a much more full featured solution than I was expecting - as one might expect from combo. His stuff is usually very clear however, whereas I'm finding it a bit hard going to understand this one. Maybe it is to do with the fact that it is close to 2:00 am local time. I think I'll try again tomorrow.

I'm still curious to know whether anybody actually has an answer to the original question. Can I simply force XL to save a workbook without an explicit path reference in cell calling an add-in function?