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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Why do you want to copy it to the users' machines rather than just installing it once from a central location?
 
Upvote 0
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.
 
Upvote 0
How are they opening it in the first place?
 
Upvote 0
I'm asking them to double click the file on the network. But going to File-Open doesn't work either.
 
Upvote 0
Have you tried using a separate workbook as a loader, rather than the add-in file itself?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
Are you specifying CopyFile:=False in your code?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top