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

[pleeseemailme]

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:

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:

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.

 

[RoryA]

Glad to help.

BTW, if you haven't already done so, I recommend reading this: Excelguru Help Site - Deploying Add-ins in a Network Environment

 

[pleeseemailme]

That is also very helpful. I was aware of most of his tricks, except for saving the add-in as read only. I see the wisdom in that. Once again, thanks so much!