i am having all kinds of problems coming up with a user-friendly
method of distributing add-ins. here's what i'm trying to do:

i have a set of statistical functions coded in a .XLA, and i have a
number of different spreadsheets that make use of those functions. i
want to distribute these files so that the user goes thru some type of
simple process to install the .XLA (hopefully just copying the file to
a particular folder), and once s/he has done that, can then seamlessly
open and use the distributed spreadsheets. further, the user will also
be creating new spreadsheets referencing these functions, which in
turn may be distributed to other people (who will also have installed
the .XLA).

i have to believe there's an easy way to accomplish this, but for the
life of me i can't find it. i've tried storing the .XLA to the users
"addins" folder and then using TOOLS | ADD-INS to set up the add-in,
but that leaves each .XLS that uses the add-in with a hard-coded
reference to the file location of the .XLA file -- and thus creates
errors when the .XLS is opened by a different user on a different
computer. i then thought i could circumvent that problem by using the
"xlstart" folder instead, but alas -- that also creates a hard-coded
reference that causes errors on a different computer where the
"xlstart" folder is in a different location.

finally, i thought i could out-fox excel by simply forcing the user to
create a specific folder (i.e., C:\EXCEL ADDINS) and storing and
referencing the add-in file there, but even that approach has
problems. when i use this approach, a spreadsheet created on a
different computer will open fine (i.e., no errors about not being
able to find the add-in), but when you close it you always get the "do
you want to save changes" message -- even if you have done absolutely
nothing at all to the spreadsheet! admittedly, this is minor but
unfortunately not very acceptable to the end users. and i also wonder
just what the ramifications of that message are -- is there something
else going on that i'm not aware of that is going to bite me in the
rear later on?

i can't believe this whole process of using add-ins is this
convoluted. any help here would be appreciated!

lee

Hi Lee,
Add-ins seem to be stored everywhere. Application.UserLibraryPath is as good
a place as any.
If your not using an installer program I suggest using a workbook as a
bootloader to...

Find the location of the bootloader. (thisworkbook.path)
Find the users Application.UserLibraryPath.
Move the add-in to Application.UserLibraryPath.
Enable the add-in...

On Error GoTo Missing
If AddIns("youradd-inname").Installed = False Then
AddIns("youradd-inname").Installed = True
End If

Kill the bootloader and your done.

This sub will dump a list of add-ins to sheet1.

Sub MyAddins()
myaddinpath = Application.UserLibraryPath
With Worksheets("sheet1")
.Rows(1).Font.Bold = True
.Range("a1:e1").Value = _
Array("Name", "Full Name", "Title", "Installed", "Path")
For i = 1 To AddIns.count
.Cells(i + 1, 1) = AddIns(i).Name
.Cells(i + 1, 2) = AddIns(i).FullName
.Cells(i + 1, 3) = AddIns(i).Title
.Cells(i + 1, 4) = AddIns(i).Installed
.Cells(i + 1, 5) = AddIns(i).Path
Next
.Range("a1").CurrentRegion.Columns.AutoFit
End With
End Sub


--

John

johnf202 at hotmail dot com


"lee hite" wrote in message
...
i am having all kinds of problems coming up with a user-friendly
method of distributing add-ins. here's what i'm trying to do:

i have a set of statistical functions coded in a .XLA, and i have a
number of different spreadsheets that make use of those functions. i
want to distribute these files so that the user goes thru some type of
simple process to install the .XLA (hopefully just copying the file to
a particular folder), and once s/he has done that, can then seamlessly
open and use the distributed spreadsheets. further, the user will also
be creating new spreadsheets referencing these functions, which in
turn may be distributed to other people (who will also have installed
the .XLA).

i have to believe there's an easy way to accomplish this, but for the
life of me i can't find it. i've tried storing the .XLA to the users
"addins" folder and then using TOOLS | ADD-INS to set up the add-in,
but that leaves each .XLS that uses the add-in with a hard-coded
reference to the file location of the .XLA file -- and thus creates
errors when the .XLS is opened by a different user on a different
computer. i then thought i could circumvent that problem by using the
"xlstart" folder instead, but alas -- that also creates a hard-coded
reference that causes errors on a different computer where the
"xlstart" folder is in a different location.

finally, i thought i could out-fox excel by simply forcing the user to
create a specific folder (i.e., C:\EXCEL ADDINS) and storing and
referencing the add-in file there, but even that approach has
problems. when i use this approach, a spreadsheet created on a
different computer will open fine (i.e., no errors about not being
able to find the add-in), but when you close it you always get the "do
you want to save changes" message -- even if you have done absolutely
nothing at all to the spreadsheet! admittedly, this is minor but
unfortunately not very acceptable to the end users. and i also wonder
just what the ramifications of that message are -- is there something
else going on that i'm not aware of that is going to bite me in the
rear later on?

i can't believe this whole process of using add-ins is this
convoluted. any help here would be appreciated!

lee

On Tue, 14 Oct 2003 08:37:56 -0400, "jaf" wrote:

Add-ins seem to be stored everywhere. Application.UserLibraryPath is as good
a place as any.
If your not using an installer program I suggest using a workbook as a
bootloader to...

Hi John -- thanks for the info, but i'm not sure that addresses the
problems that i'm trying to solve -- or maybe it does and i'm missing
exactly what your code accomplishes. specifically the problems a

1) excel hard codes references to functions in add-ins, so that when i
distribute a .XLS with a function reference from the add-in, if the
add-in is not stored in a folder with the exact same name as on the
computer where the .XLS was created, then the user gets an error
message indicating that the file could not be found.

and that's the problem with using something like
Application.UserLibraryPath as the add-in folder -- that's going to be
different for each computer/user combination, causing distributed
spreadsheets to fail.

it would seem to me that the "proper" way to implement add-in
functionality would be to indeed use a standardized folder such as
what you suggest, and have Excel always search that folder to locate
add-ins (regardless of the name of the folder) -- in much the same
manner that an executable searches the windows system folder for
..DLL's. but alas, best i can tell, Excel just doesn't work that way.

2) the other problem is that any spreadsheet that i have that
references a function that's within an add-in opens with the
Document.Saved property set to False. thus if a user simply opens one
of these files, looks at something and then immediately closes the
file, s/he gets the "Do you want to save changes" prompt -- which
causes all kinds of confusion, since the file wasn't changed. i don't
understand why that is happening, nor can i find a workaround.

lee



Find the location of the bootloader. (thisworkbook.path)
Find the users Application.UserLibraryPath.
Move the add-in to Application.UserLibraryPath.
Enable the add-in...

On Error GoTo Missing
If AddIns("youradd-inname").Installed = False Then
AddIns("youradd-inname").Installed = True
End If

Kill the bootloader and your done.

This sub will dump a list of add-ins to sheet1.

Sub MyAddins()
myaddinpath = Application.UserLibraryPath
With Worksheets("sheet1")
.Rows(1).Font.Bold = True
.Range("a1:e1").Value = _
Array("Name", "Full Name", "Title", "Installed", "Path")
For i = 1 To AddIns.count
.Cells(i + 1, 1) = AddIns(i).Name
.Cells(i + 1, 2) = AddIns(i).FullName
.Cells(i + 1, 3) = AddIns(i).Title
.Cells(i + 1, 4) = AddIns(i).Installed
.Cells(i + 1, 5) = AddIns(i).Path
Next
.Range("a1").CurrentRegion.Columns.AutoFit
End With
End Sub

Place the add-in in what your computer returns for
Application.UserLibraryPath when you build it.
In your code always use Application.UserLibraryPath in ref. to your add-in.

No idea on the second question.
--

John

johnf202 at hotmail dot com


"lee hite" wrote in message
...
On Tue, 14 Oct 2003 08:37:56 -0400, "jaf" wrote:

Add-ins seem to be stored everywhere. Application.UserLibraryPath is as
good
a place as any.
If your not using an installer program I suggest using a workbook as a
bootloader to...

Hi John -- thanks for the info, but i'm not sure that addresses the
problems that i'm trying to solve -- or maybe it does and i'm missing
exactly what your code accomplishes. specifically the problems a

1) excel hard codes references to functions in add-ins, so that when i
distribute a .XLS with a function reference from the add-in, if the
add-in is not stored in a folder with the exact same name as on the
computer where the .XLS was created, then the user gets an error
message indicating that the file could not be found.

and that's the problem with using something like
Application.UserLibraryPath as the add-in folder -- that's going to be
different for each computer/user combination, causing distributed
spreadsheets to fail.

it would seem to me that the "proper" way to implement add-in
functionality would be to indeed use a standardized folder such as
what you suggest, and have Excel always search that folder to locate
add-ins (regardless of the name of the folder) -- in much the same
manner that an executable searches the windows system folder for
.DLL's. but alas, best i can tell, Excel just doesn't work that way.

2) the other problem is that any spreadsheet that i have that
references a function that's within an add-in opens with the
Document.Saved property set to False. thus if a user simply opens one
of these files, looks at something and then immediately closes the
file, s/he gets the "Do you want to save changes" prompt -- which
causes all kinds of confusion, since the file wasn't changed. i don't
understand why that is happening, nor can i find a workaround.

lee



Find the location of the bootloader. (thisworkbook.path)
Find the users Application.UserLibraryPath.
Move the add-in to Application.UserLibraryPath.
Enable the add-in...

On Error GoTo Missing
If AddIns("youradd-inname").Installed = False Then
AddIns("youradd-inname").Installed = True
End If

Kill the bootloader and your done.

This sub will dump a list of add-ins to sheet1.

Sub MyAddins()
myaddinpath = Application.UserLibraryPath
With Worksheets("sheet1")
.Rows(1).Font.Bold = True
.Range("a1:e1").Value = _
Array("Name", "Full Name", "Title", "Installed", "Path")
For i = 1 To AddIns.count
.Cells(i + 1, 1) = AddIns(i).Name
.Cells(i + 1, 2) = AddIns(i).FullName
.Cells(i + 1, 3) = AddIns(i).Title
.Cells(i + 1, 4) = AddIns(i).Installed
.Cells(i + 1, 5) = AddIns(i).Path
Next
.Range("a1").CurrentRegion.Columns.AutoFit
End With
End Sub

On Tue, 14 Oct 2003 13:01:12 -0400, "jaf" wrote:

Place the add-in in what your computer returns for
Application.UserLibraryPath when you build it.
In your code always use Application.UserLibraryPath in ref. to your add-in.

John -- i'm not using the add-in functions in code -- they are being
used in cell values. for example, i have a function that calculates a
specialized average called CAVG -- so a given cell might have the
value CAVG(A1:A100,B1:B100,D1). i don't think i can use
Application.UserLibraryPath in the cell reference???

lee

Isn't your add-in written with code? Does it refer to itself? What path does
it use?



--

John

johnf202 at hotmail dot com


"lee hite" wrote in message
...
On Tue, 14 Oct 2003 13:01:12 -0400, "jaf" wrote:

Place the add-in in what your computer returns for
Application.UserLibraryPath when you build it.
In your code always use Application.UserLibraryPath in ref. to your
add-in.

John -- i'm not using the add-in functions in code -- they are being
used in cell values. for example, i have a function that calculates a
specialized average called CAVG -- so a given cell might have the
value CAVG(A1:A100,B1:B100,D1). i don't think i can use
Application.UserLibraryPath in the cell reference???

lee

On Tue, 14 Oct 2003 15:21:05 -0400, "jaf" wrote:

Isn't your add-in written with code?

John -- yes.....

Does it refer to itself? What path does
it use?

not really. it's just a bunch of user defined functions.

lee