Excel Add-Ins Sometimes Do Not Load

2
votes

I have a few excel add-ins that fail to load sometimes when I open an existing workbook. Here are some of the pertinent facts and things I have noticed when trying to diagnose:

  • These are regular excel add-ins, not COM add-ins.
  • Add-ins always load when opening excel from the program menu.
  • Add-ins do not load ~10-20% of the time when opening a workbook from email/shared drive/personal drive.
  • I am unable to consistently replicate the issue. Sometimes the opening the same workbook will load the add-ins, sometime it won't.

  • The issue might be connected to one of the toolkits that has an installer built into it. (the idea behind this add-in is that it can be sent to people, and they can install it just by opening. Then, once it's installed in the AddIns folder, it no longer does anything when the add-in is loaded).

    Private Sub Workbook_Open()

If Right(ThisWorkbook.Path, 6) <> "AddIns" Then

 Dim blRegister As Boolean
 Dim ThisAddIn As AddIn

 Application.DisplayAlerts = False
 On Error Resume Next

 ThisWorkbook.Sheets("Installing").Activate

 Application.AddIns("Toolkit").Installed = False

 ThisWorkbook.SaveAs "Z:\AppData\Microsoft\AddIns\ToolKit.xlam", AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges, FileFormat:=55

 On Error GoTo 0

 blRegister = Application.RegisterXLL("Z:\AppData\Microsoft\AddIns\ToolKit.xlam")

 Set ThisAddIn = Application.AddIns.Add("Z:\AppData\Microsoft\AddIns\ToolKit.xlam")
 ThisAddIn.Installed = True

 MsgBox "Thanks for Installing the Toolkit!"
 ThisWorkbook.Close False

 Application.DisplayAlerts = True

End If

End Sub

Any help or insight would be greatly appreciated.

1
excelvba
So you are assuming "Z:\" always present and accessible on all the computers? Normally AddIns are stored in partition with Operating System (C:). - PatricK
Correct, in this case Z: is accessible on all computers (equivalent of C: drive) - user3738961
May be you need to do more before the SaveAs. You may need to Close the addin workbook that the AddIns("Toolkit").Name refers to when AddIns("Toolkit").IsOpen = True, then Application.EnableEvents = False, open the addin after ThisAddIn.Installed = True and then Application.EnableEvents = True before ThisWorkbook.Close. The thing is, unless Debug.Print is used on every step, you can't tell where it fails. - PatricK
To tell where it fails add line number and include erl in your output. And run the sub via an ontimer in a Module. - Tuntable

1 Answers

0
votes

I have this problem. JKP says maybe cause by download from internet flag. Not my problem though. http://www.jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp