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