I have a project using an own Addin MyUdf.xlam. The project and the Addin locate in Dropbox and are used by three different users: HBJ, KOKKO and AIB. I have to install the AddIn and be sure that the most current Addin version is available at any time for all users. Both the application and the Addin are in Dropbox folders, the name of which is related to the user name and therefore different for all users. The procedures in the AddIn work perfectly but the functions make me crazy.
I use an AddIn function CheckDigit(parameter) . If user KOKKO opens a file recently modified and saved by user HBJ, the call to the function CheckDigit() looks something like €œC:\USERS\HBJ\DROPBOX\COMMON\MyUdf!CheckDigit(pa rameter)€�, and result is #VALUE!.

I thought I could, when I open the project, copy the Addin to the local Addin folder and use it locally €“ not the version in Dropbox, but that folder is protected.
Below how I load the AddIn in WorkBook.Open()
Set myAddIn = AddIns.Add(Filename:=cFile)
myAddIn.Installed = True

€˜Load known XLAM
For Each myAddIn In AddIns
If myAddIn.Name = "MyUdf.xlam" Then
myAddIn.Installed = False
myAddIn.Installed = True
End If
Next
I need help on how to install and load my AddIn in that environment.

/HÃ¥kan

Here's a vbScript that will install an addin for all users to every version of
Excel installed on the machine running the script by writing directly to the
appropriate Excel Addin Registry keys. Copy the code to a text file named
Install_MyUdf.xlam.vbs on your Desktop (or wherever you want to store it);
-double-click it to execute.

Note that addins do not have to be located in the Addins folder under the user
profile. Edit the sAddinPath string to point your MyUdf.xlam where you store
it.


Dim sAddinPath, sKey, sOpen, n, i
Dim vSZ, oWMI, vVer, vValNames, vValTypes

Const sVers = "11.0,12.0,14.0,15.0,16.0"
Const sOfficeKey = "Software\Microsoft\Office\"
Const sOptsKey = "\Excel\Options"
Const sAddinMngr = "\Excel\Add-in Manager"
Const HKCU = &H80000001
Const REG_vSZ = 1
sAddinPath = Chr(34) & "C:\MyFolder\APP\app.xlam" & Chr(34)

Set oWMI = GetObject("winmgmts:root\default:StdRegProv")
For Each vVer In Split(sVers, ",")
sKey = sOfficeKey & vVer & sOptsKey
oWMI.EnumValues HKCU, sKey, vValNames, vValTypes
If IsArray(vValNames) Then '//confirm version is installed
n = 1: sOpen = "OPEN"
For i = LBound(vValNames) To UBound(vValNames)
oWMI.GetStringValue HKCU, sKey, vValNames(i), vSZ
If Left(vValNames(i), 4) = "OPEN" Then
n = n + 1
If vSZ = sAddinPath Then
n = 0: Exit For
End If
End If 'Left(vValNames(i), 4) = "OPEN"
Next 'i

If n 0 Then
n = n - 1
If n 0 Then sOpen = sOpen & n
oWMI.SetStringValue HKCU, sKey, sOpen, sAddinPath

'Make sure to remove it from Add-in Manager
sKey = sOfficeKey & vVer & sAddinMngr
oWMI.DeleteValue HKCU, sKey, Replace(sAddinPath, Chr(34), "")
End If 'n 0
End If 'If IsArray(vValNames)
Next 'vVer
Set oWMI = Nothing

I also have an app named AddMan4XL.exe which is designed to manage workbook
addins for all versions of Excel on the host machine. It discovers all the
installed versions and lets you toggle their 'loaded' status for each
individual version. You can download its userguide to see how it works he

https://app.box.com/s/xhpauhxphpigvzafx8a7krid133iurnd

AddMan4XL needs to be updated so if you're interested to test drive it free for
30 days, post back and I'll 'git-r-done'!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, March 15, 2018 at 6:07:51 PM UTC+2, hbj wrote:
I have a project using an own Addin MyUdf.xlam. The project and the Addin locate in Dropbox and are used by three different users: HBJ, KOKKO and AIB. I have to install the AddIn and be sure that the most current Addin version is available at any time for all users. Both the application and the Addin are in Dropbox folders, the name of which is related to the user name and therefore different for all users. The procedures in the AddIn work perfectly but the functions make me crazy.
I use an AddIn function CheckDigit(parameter) . If user KOKKO opens a file recently modified and saved by user HBJ, the call to the function CheckDigit() looks something like €œC:\USERS\HBJ\DROPBOX\COMMON\MyUdf!CheckDigit(pa rameter)€�, and result is #VALUE!.

I thought I could, when I open the project, copy the Addin to the local Addin folder and use it locally €“ not the version in Dropbox, but that folder is protected.
Below how I load the AddIn in WorkBook.Open()
Set myAddIn = AddIns.Add(Filename:=cFile)
myAddIn.Installed = True

€˜Load known XLAM
For Each myAddIn In AddIns
If myAddIn.Name = "MyUdf.xlam" Then
myAddIn.Installed = False
myAddIn.Installed = True
End If
Next
I need help on how to install and load my AddIn in that environment.

/HÃ¥kan

As far as I understood, every user has to explicitly execute the script to activate the AddIn, when the addIn has changed. If so, this is not what I require of my project.
Is it quite impossible to handle AddIn installation and updates automatically in WorkBook.Open() €“ and remove it on closing the application.
I decided to solve this problem the quick-and-dirty way: I add to the WorkBook.Open Substitute the Dropbox folder containing the AddIn to i.e S:. After this, my application will find the Addin in S:
(SUBST S: C:\USERS\DROPBOX\KOKKO\COMMON)

/HÃ¥kan

On Saturday, March 17, 2018 at 12:40:09 PM UTC+2, hbj wrote:
On Thursday, March 15, 2018 at 6:07:51 PM UTC+2, hbj wrote:
I have a project using an own Addin MyUdf.xlam. The project and the Addin locate in Dropbox and are used by three different users: HBJ, KOKKO and AIB. I have to install the AddIn and be sure that the most current Addin version is available at any time for all users. Both the application and the Addin are in Dropbox folders, the name of which is related to the user name and therefore different for all users. The procedures in the AddIn work perfectly but the functions make me crazy.
I use an AddIn function CheckDigit(parameter) . If user KOKKO opens a file recently modified and saved by user HBJ, the call to the function CheckDigit() looks something like €œC:\USERS\HBJ\DROPBOX\COMMON\MyUdf!CheckDigit(pa rameter)€�, and result is #VALUE!.

I thought I could, when I open the project, copy the Addin to the local Addin folder and use it locally €“ not the version in Dropbox, but that folder is protected.
Below how I load the AddIn in WorkBook.Open()
Set myAddIn = AddIns.Add(Filename:=cFile)
myAddIn.Installed = True

€˜Load known XLAM
For Each myAddIn In AddIns
If myAddIn.Name = "MyUdf.xlam" Then
myAddIn.Installed = False
myAddIn.Installed = True
End If
Next
I need help on how to install and load my AddIn in that environment.

/HÃ¥kan

As far as I understood, every user has to explicitly execute the script to activate the AddIn, when the addIn has changed. If so, this is not what I require of my project.
Is it quite impossible to handle AddIn installation and updates automatically in WorkBook.Open() €“ and remove it on closing the application.
I decided to solve this problem the quick-and-dirty way: I add to the WorkBook.Open Substitute the Dropbox folder containing the AddIn to i.e S:. After this, my application will find the Addin in S:
(SUBST S: C:\USERS\DROPBOX\KOKKO\COMMON)

/HÃ¥kan

The command SUBST was TOO dirty! It still used the real path. I gave up and moved the Addin to a server and installed it manually in each workstation - until I find a sufficient solution.

/HÃ¥kan

As far as I understood, every user has to explicitly execute the script to
activate the AddIn, when the addIn has changed. If so, this is not what I
require of my project.

As long as the user downloads the addin to the same location, they'll only need
to run the script once so long as the addin's name is constant.

Alternatively, the user can install the addin via the normal way; the Excel
Options dialog.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion