Solved

activate Excel addin

Posted on 2014-04-29
15
992 Views
Last Modified: 2014-05-06
is there any way to activate an excel addin via registry or commandline?  I need to push out an addin to all users on the network.

Just copying it to the addins folder adds it to excel but the user still has to stumble through options - addins to activate it.
0
Comment
Question by:livegirllove
  • 8
  • 5
  • 2
15 Comments
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40031283
Are the users on Windows Server?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40031285
no.  multiple workstations.
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40031328
As far as I know, this is not possible.

If it is a Windows Server network, this is possible.
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40031417
how would it be done on a server?  this is a Microsoft windows network with a server/domain controller.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40031687
An AddIn can be loaded or unloaded (clicking the check box) with VBA.   Application.AddIns.Add has a .Installed that can be set to True or False to load or unload the AddIn.

Just as a suggestion... you can use the code below in an Workbook_Open Event that will install or update the Add-In on the User PC.   If there is a common network location the users have access to, put the Add-In.xlsm and a separate installer file with the code below in that location.   Send the users an email with a hyperlink to the installer.   It will copy the AddIn to their UserLibraryPath location and then enable the Add-In on their PC.   If you have future updates, just overlay the XLSM file on the network and have them run the installer again.   It will unload it (uncheck the box), copy the new file to their PC and then load the new version (check the box).  

If the Add-In has its own Ribbon you can add a button that will call the installer so the user can update the Add-In from the Ribbon whenever you notify them a new version exists.

HTH,
Jerry
Option Explicit

'---------------------------------------------------------------------------------------
' Module    : ThisWorkbook
' Author    : ProdOps
' Date      : 04/30/2014
' Purpose   : Installs or Updates the Excel Add-In on the Users PC
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

    Dim AI As Excel.AddIn
    Dim Response As Long
    
    Response = MsgBox("Please verify you want to install or update the Excel Add-In", vbYesNo, "Install / Update Add-In")
        
    Select Case Response
        Case vbYes
        Case vbNo:   GoTo noSave
        Case Else:   GoTo noSave
    End Select
    
    On Error GoTo copyfile
    
    ' This section assumes the AddIn exists and it is being updated.
    ' The intent is to unload it so it can be replaced with a new version of the AddIn file.
    ' Set AI to be the Excel Add-In in the user Application.UserLibraryPath directory on their PC
    ' If it does not exist yet, the Set will error and force a jump the next section
    Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "Excel Add-In.xlam")
    ' If it is currently loaded then Unload it so a new copy can be copied to that location
    If AI.Installed = True Then AI.Installed = False
  
copyfile:
    On Error GoTo 0
    ' Copy the file located on the Network Share to the User Application.UserLibraryPath directory on their PC
    ' If this is the first install it will add the file to the users AddIn directory
    ' If it currently exist and this is an update it will replace the existing AddIn file
    FileCopy "\\Network\Common\ExcelAdmin\Excel Add-In.xlam", Application.UserLibraryPath & "Excel Add-In.xlam"
    ' Load the new Add-In file that was just copied to the user PC
    Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "Excel Add-In.xlam")
    AI.Installed = True
    MsgBox "The Excel Add-In has been Installed / Updated Successfully", vbOKOnly, Title:="Excel Add-In Installation / Update"
    
noSave:
    ThisWorkbook.Close False
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:livegirllove
ID: 40031698
that looks like it will work.  well the theory anyway.  
How do I create this installer file?  Where do I add the code you gave?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40031726
well I made a little progress.  added the developer toolbar and opened the VB editor.  Added your code to the workbook sheet.
if I open testbook.xlsm it prompts to update the file.  It will copy the addin.xla to the proper folder but then it pops up a runtime error.
1004
unable to set the installed property of the addin class.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40031783
Did you change all occurrences of "Excel AddIn" in the code to the name of your AddIn?

In the VB Editor Immediate window (Ctrl-G) or View/Immediate Window...  type
?application.UserLibraryPath
Does this match the directory on the PC where you expect the AddIn to be copied?

In the statement...
Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "Excel Add-In.xlam")
Did you change the "Excel Add-In.xlam" to the name of your AddIn?

Are you getting the error on this statement?
AI.Installed = True

In your AddIn VBA Project, open the VB Editor and click ThisWorkbook.  In the Properties section, is the property IsAddin set to TRUE?
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40031812
isaddin was false.  I changed it to true and it made me change the file type of the workbook to xlam.

it errors on this line
Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "inchcalc.xla")

I did change all the instance names to my addins name.

It does copy the file to  the proper place on the computer and the addin is available just unactivated.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40032014
Your post says:
made me change the file type of the workbook to xlam
and...
Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "inchcalc.xla")
Is the name of your file inchcalc.xla or inchcalc.xlam?    If the file name is xlam that would cause the SET statement to fail since it is looking for the wrong file name
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40032867
I have a workbook testbook1.xls
I open the VB ribbon bar and try to add your code to the workbook sheet.
It makes me change the book to an xlsm

I change it from isaddin=false to isaddin=true.  it now makes me change the workbook to a xlam.

the name of the actual addin I want loaded is inchcalc.xla.

If I open testbook1.xlam it copies inchcalc.xla to the proper addins folder and then throws the error.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 40033760
I only have Excel 2013 available to work with and it appears you are working with Excel 2003 file formats (*.xla) so there may be incompatibilities between the two that I am not aware of and don't have the software to compare.  

After FileCopy, there are only two statements.  The Set Statement and the AI.Install.  Can you please tell me which one is failing and please give the full error message?   Error number and Error description text.  You can step through the code execution line by line by pressing the F8 key to execute each line.

In the VBE Immediate Window, please execute the follow statement and let me know what the result is.        ?Application.UserLibraryPath      Please indicate if this is the directory where  the inchcalc.xla file is located?   The Set statement will fail if the addin is not located in this directory or if the addin name is misspelled.  

If the error is on the SET statement, perhaps recode the statement to include the specific directory where the file is located instead of using UserLibraryPath.  On my computer it would be
Set AI = Application.AddIns.Add(Filename:="C:\Users\Jerry\AppData\Roaming\Microsoft\AddIns\inchcalc.xla")

Open in new window

Code it to match your directory path as possible debug step.

Does the "InchCalc" Add-In show in the Add-In box under Excel Options where you would normally "check the box"?   The AI.Installed = True or AI.Installed = False statement is just a toggle to check or uncheck the box as long as the Addin is recognized as a valid addin.

Also, I found this webpage helpful when working with Addin installs:
http://www.cpearson.com/excel/installinganxla.aspx
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40036669
run time error 1004
unable to get the Add property of the addin class

When I click debug this line is highlighted in yellow:
    Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "InchCalc.xla")

 ?Application.UserLibraryPath      
returns:  C:\Users\myname\AppData\Roaming\Microsoft\AddIns\

InchCalc.xla is in that directory so it does the copy part correctly.

Does the "InchCalc" Add-In show in the Add-In box under Excel Options where you would normally "check the box"?  
-yes

And if I check the box it does activate.

I'm using Excel 2013 as well.
the addin comes from http://josh.com/InchCalc
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 40037844
The statement:
Set AI = Application.AddIns.Add(Filename:=Application.UserLibraryPath & "InchCalc.xla")
Can generate Error 1004, "Unable to get the Add property of the addin class"  when the path or file name cannot be resolved.  I generated a similar message shown in the picture below where the file extension was incorrect.   I am sure you have already cross checked your files and locations but this is the only way I was able to generate the same error.

I downloaded the InchCalc addin from the Josh website and have successfully incorporated it into the code I posted before.  The only change you would have to make is to replace the network location where your master file is located. Replace:
\\NetworkShare\Common\ExcelAdmin\InchCalc.xla with your network location.

It works correctly for me to install the first time and also as an update when the file already exists in the users AddIn directory.  It successfully unloads and reloads the AddIn every time.
Error 1004
'---------------------------------------------------------------------------------------
' Module    : ThisWorkbook
' Author    : ProdOps
' Date      : 04/30/2014
' Purpose   : Installs or Updates the InchCalc Add-In on the Users PC
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

    Dim AI As Excel.AddIn
    Dim Response As Long
    
    Response = MsgBox("Please verify you want to install or update the InchCalc Add-In", vbYesNo, "Install / Update Add-In")
        
    Select Case Response
        Case vbYes
        Case vbNo:   GoTo noSave
        Case Else:   GoTo noSave
    End Select
    
    On Error GoTo copyfile
    
    ' This section assumes the AddIn exists and it is being updated.
    ' The intent is to unload it so it can be replaced with a new version of the AddIn file.
    ' Set AI to be the InchCalc Add-In in the user Application.UserLibraryPath directory on their PC
    ' If it does not exist yet, the Set will error and force a jump the next section
    Set AI = Application.AddIns.Add(FileName:=Application.UserLibraryPath & "InchCalc.xla")
    ' If it is currently loaded then Unload it so a new copy can be copied to that location
    If AI.Installed = True Then AI.Installed = False
  
copyfile:
    On Error GoTo 0
    ' Copy the file located on the Network Share to the User Application.UserLibraryPath directory on their PC
    ' If this is the first install it will add the file to the users AddIn directory
    ' If it currently exist and this is an update it will replace the existing AddIn file
    FileCopy "\\NetworkShare\Common\ExcelAdmin\InchCalc.xla", Application.UserLibraryPath & "InchCalc.xla"
    ' Load the new Add-In file that was just copied to the user PC
    Set AI = Application.AddIns.Add(FileName:=Application.UserLibraryPath & "InchCalc.xla")
    AI.Installed = True
    MsgBox "The InchCalc Add-In has been Installed / Updated Successfully", vbOKOnly, Title:="InchCalc Add-In Installation / Update"
    
noSave:
    ThisWorkbook.Close False
 
End Sub

Open in new window

If you still cannot get the code to work you are welcome to close this question and reopen a new one for another person to help you.   The main thing is for you to get your problem resolved and if this is not helping you I fully understand and don't take it personal.  

Thanks,
Jerry
0
 
LVL 1

Author Comment

by:livegirllove
ID: 40043840
cut and pasted and changed the location.  worked fine.  thanks.
I think the first time I used a local path eg e:\installs\inchcalc.xla. Yes the path is valid on this computer and it was just for testing.  this time I used a unc path and it worked.  so maybe that was the problem all along or you changed something.  either way thanks a lot!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now