using a *.xslm add-in with several users

[phij]

Hi,

I wrote an Excel add-in which I want two distinct users to use. It all works
perfectly fine while working locally with one user. It loads on startup and
returns the correct values while one user uses it. However if I use the
function contained in the add-in in a spreadsheet which i save and then send
to a colleague who also has the add-in installed the cell no longer returns
the correct Value, but tries to find the add-in at the path where it was
saved on the first user's computer.

How can I correctly use functions defined in an add-in across different
users who both have the add-in installed?

 

[Dave Peterson]

I think the easiest thing to do is to tell each (all!) recipients to put the
addin in the same folder name:

C:\PhijsAddins\youraddinname.xla

Then excel will always look to that path.

Yep, you'll have to teach them to use Edit|Links (xl2003 menus) for the
workbooks that they've already started.

 

[phij]

Hi Dave,

thanks for the quick reply. That solves the Problem. However saving the
add-in to C:\ or C:\PhijsAddins\ doesn't seem to be the professional way.
However saving it to C:\Program Files\PhijsAddins\ resulted in the same
problem as the Program files folder is called "Programme" in the German
version and therefore the folder names don't correspond to 100%. How do the
big Corporates handle this problem as they usually use folders such as
C:\Program Files\Oracle\...? Or is there another approach that the Add-in can
be stored in individual folders differing by the user which probably would be
the best solution.

Thanks a lot!

Philipp

 

[joel]

Use the environmental varible UserProfile


Folder = environ("APPData")

This will return the following without a slash at the end.

C:\Documents and Settings\joel\Application Data



So you would want to do this

FName = Folder & "\book1.xla"


To get a list of all the environmental variables do the following

Start - Run
Enter : cmd.exe
In Dos Window type : Se

 

[K_Macd]

Programming in VBA is a bit like an Irish road map - where you have to start
from is not necessarily the best spot. I have had some success with
installing addins to a folder on a central server although there is a slight
performance reduction. The trick is to make sure that once the reference is
set that the path will be the same from any workstation

 

[K_Macd]

Testing

 

[Dave Peterson]

I have no idea how big corporate IT departments handle this--but I wouldn't be
surprised if they use the same technique, but use a nicer name:

C:\MonolithInc\Excel\Addins\someaddinhere.xlam

Another (I think more complex) way to avoid the problem is to change the links
each time the workbook opens.

You could tell the user to put the addin in the same folder as the workbook and
use that path in the .changelinks line.

Search google for few examples.

 

[Dave Peterson]

One thing I bet they do utilize for somethings is a common location for their
addins.

Put their files on:
\\server\sharename\monolithinc\excel\addins\....

Then tell people to access the addin via the UNC path.

 

[phij]

I already thought of using the environmental variable. However as it seems
Excel saves the path to functions in add-ins absolute, not relatively in the
file. So the problem is not returning the environmental variable in the VBA
code, but that the second user's Excel can not find the function in the
add-in at all. Even when the add-in is loaded (but saved in another
location). Saving the add-in in the user's folder or the excel standard
folder also returned the same problem.

How is this problem being solved in the standard Excel add-ins Eurotool, or
Solver? Eurovonvert() for example definitely work across different users and
Excel versions (which means different folders of the file)

 

[joel]

Fix the add-in. Post the code if you need help for the add-in

 

[phij]

You will be disappointed at present it is just a function returning the
formula of a cell. However as I said it is supposed to get used across many
users and convenience in installation and use is central.

Public Function FORMELN(Zelle As Range) As String
Application.Volatile True
FORMELN = Application.Text(Zelle.FormulaLocal, "")

End Function

Using .changelinks in combination with the environmental varible
ProgramFiles sounds like a possible solution. However I then run into the
problem of running the function for each new workbook opened. As all code
should be in the add-in, none in the individual files.

 

[phij]

You will be disappointed at present it is just a function returning the
formula of a cell. However as I said it is supposed to get used across many
users and convenience in installation and use is central.

Public Function FORMELN(Zelle As Range) As String
Application.Volatile True
FORMELN = Application.Text(Zelle.FormulaLocal, "")

End Function

Using .changelinks in combination with the environmental varible
ProgramFiles sounds like a possible solution. However I then run into the
problem of running the function for each new workbook opened. As all code
should be in the add-in, none in the individual files.

 

[Dave Peterson]

I would use a folder with the exact name.

You will be disappointed at present it is just a function returning the
formula of a cell. However as I said it is supposed to get used across many
users and convenience in installation and use is central.

Public Function FORMELN(Zelle As Range) As String
Application.Volatile True
FORMELN = Application.Text(Zelle.FormulaLocal, "")

End Function

Using .changelinks in combination with the environmental varible
ProgramFiles sounds like a possible solution. However I then run into the
problem of running the function for each new workbook opened. As all code
should be in the add-in, none in the individual files.

 

[phij]

Thanks a lot, I guess I'll do that for now.
However a solution therfore would be great for a future version.

Philipp

 

[Dave Peterson]

That is the solution I'd use--no matter what version.