Use VBA to install an Add-in from network - unable to add it to the add-ins collection

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
Howdy,

I've spent the past few days researching this and I can't figure out why my code is crashing. I have an add-in I've made that I want to distribute to other co-workers. I've placed the add-in on the network and in the workbook open event I have some code to install the add-in. The code is:
Code:
Dim NewAi as AddIn
Set NewAi = _Application.AddIns.Add("filepath" & ThisWorkbook.Name, CopyFile:=True)
NewAi.Installed = True

The code could crash at the first line - unable to get the Add property of the AddIns class. I tried modifying the code to this:
Code:
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Application.UserLibraryPath & ThisWorkbook.Name, xlOpenXMLAddIn
Application.DisplayAlerts = True
Set NewAi = _
Application.AddIns.Add(Application.UserLibraryPath & ThisWorkbook.Name)
NewAi.Installed = True

Same thing. However, running it this way, when I open the network add-in again, it works. In other words: the first time it crashes, the second time it works.

My guess is what is happening is this: because the add-in isn't in the Library Path when Excel is opened, its not able to add the add-in to the collection. This is why when my second code runs the second time, it works - because the application recognizes an add-in file in the Library Path.

From all my reading online about this subject, this should not be the case. Using the first code should add the add-in to the collection.

So, does anyone have any thoughts on how to fix, or work around this problem? Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Why do you want to copy it to the users' machines rather than just installing it once from a central location?
 

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
Well, To tell the first part of the story: that's what I first tried to do. I kept getting the same error, so I tried moving it to their machines. When this also didn't help, and hours of reading online didn't help, I decided to appeal to the experts here. If we can keep in on the network that is fine. I just tried moving it because I thought it would work better if it was in the proper location. But something else is going on.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How are they opening it in the first place?
 

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201

ADVERTISEMENT

I'm asking them to double click the file on the network. But going to File-Open doesn't work either.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Have you tried using a separate workbook as a loader, rather than the add-in file itself?
 

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201

ADVERTISEMENT

RoryA, I just tried that on two machines and that it seems to be working. I won't be able to test more machines until after lunch. Do you know why using another workbook to programmatically open the .xlam file work while directly opening it does not?
 

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
RoryA, new development, if you have a moment. When using another workbook to open and install the add-in Excel prompts the user with a dialog box asking if they want to copy the add-in to the add-in library with a yes-no-cancel option. Since we want this to remain on the network we want them to click no. In case someone doesn't follow directions, I wanted to control this, so I modified to code and set the DisplayAlerts property to False before the dialog box came up. Instead of defaulting to the default option, the code crashes again with the run-time error "Unable to copy add-in to library". Do you know of another way around this, by chance? Again, thanks for your help on this.

For what it's worth, it may be useful to mention that when the user selects "Cancel" in the dialog box the code crashes with the ever familiar "Unable to get the add-in property of the add-ins class" error.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,500
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you specifying CopyFile:=False in your code?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,017
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top