AddIns.Add statement throwing an Internal Error 51-CodePudding

I am trying to install addins programmatically (more precisely, it is automated version update on Workbook_Open event) but I have run into an issue with the AddIns.Add method, which just "does not work". I copy the desired adding into C:\Users\[username]\Documents\Addins and then feed the full filepath to AddIns.Add, however the addin is not added, as evidenced by the subsequent statement failing (subscript out of range, the name of the supposedly added addin does not exist).

During the install attempt, the execution simply runs through the AddIns.Add without any issue (except the result) but on stepping through, I am getting Internal error (Error 51). I have tried a number of ways to work around that, add Application.Wait before and after the AddIns.Add to make sure it has sufficient time, putting it into a Do While Loop statement to attempt multiple executions, but to no avail.

AddIns.Add Filename:=sInstallPath & sFile
AddIns(sAddinFullName).Installed = True

Btw this worked until yesterday, when I did a couple codes updates but not even remotely close to this area. I think I had some issues with this in past because the statement was envelopped by Application.Wait (Now TimeValue("0:00:01")), which I think resolved probably a similar issue but I cannot recall that any more.

Edit: Adding a broader part of the code - a function that does the installation proper and on success, returns True.

Function InstallAddin(sFullPath, sAddinName) As Boolean    
    Dim oAddin As Object
    Dim bAdded As Boolean
    Dim i As Integer
    
    Do Until bAdded = True Or i = 10        
        For Each oAddin In AddIns
                If oAddin.Name = sAddinName Then
                    bAdded = True
                    Exit For
                End If
        Next oAddin
    
        If bAdded = False Then
            'Application.Wait (Now   TimeValue("0:00:01"))
            AddIns.Add Filename:=sFullPath, CopyFile:=False
            Debug.Print "Attempt " & i
            'Application.Wait (Now   TimeValue("0:00:01"))
        End If
        i = i   1
    Loop
    
    If bAdded = True Then
        'disable events to prevent recurrence - installing addin counts as opening its workbook
        Application.EnableEvents = False
        AddIns(sAddinName).Installed = True
        Application.EnableEvents = True
        
        InstallAddin = True
    End If

End Function

sFullPath : "C:\Users\Eleshar\Documents\Addins\MyAddin - v.0.25.xlam"

sAddinName : "MyAddin - v.0.25"

The "MyAddin - v.0.25.xlam" file is present in the installation path.

There is a piece of code elsewhere, which ensures that a regular WB is open during this event.

Edit 2: The full functionality of the macro is:

On opening the file by a user, offering self-install.
On opening the file by a user, checking for previous installed versions, offering self-installation (after which it removes the old versions, including itself).
On Workbook_Open, checking a Sharepoint repository for any new versions, offering to install the newest one available and removing any older versions including itself.

Edit 3: So I found an interesting thing... AddIns.Add does not seem to work when executed from the code (the addin does not get listed in Developer > Addins). However when I type the same exact statement into the immediate window during the execution, it works and then the addin can get installed...

CodePudding user response：

Since you do not show all your used code, please try the next one. I am using it to auto install the add-ins I design:

Private Sub Workbook_Open()
  Dim Name As String, tmp As Boolean, n As Boolean, Merk As String
   Name = ThisWorkbook.BuiltinDocumentProperties(1) '(1)
   On Error Resume Next
   tmp = AddIns(Name).Installed
    If Err.Number <> 0 Then
      Err.Clear: On Error GoTo 0
         If Workbooks.Count = 0 Then n = True
             If n Then
                 Workbooks.Add
                 Merk = ActiveWorkbook.Name
             End If
             AddIns.Add FileName:=ThisWorkbook.FullName
             AddIns(Name).Installed = True
             If n Then Workbooks(Merk).Close False
    End If
    On Error GoTo 0
End Sub

'(1) it represents the Add-inn title. It can be set programmatically or manual in Properties - Details - Title. When add-in is not open!

CodePudding user response：

So I did not really figure out the issue with AddIns.Add, however I worked around that but having the macro directly edit the Excel registry keys to install the add in.

Sub AddinInstall(sAddinName As String, ByVal sFullPath As String)
    Dim oShell As Object: Set oShell = CreateObject("WScript.Shell")
    Dim i As Integer: i = 0
    Dim iIndex As Integer
    Dim sRegKey As String: sRegKey = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options\OPEN"
    Dim sSZ As String
    sFullPath = ChrW(34) & sFullPath & ChrW(34)
    
    On Error Resume Next
    Do 'loop through registry keys (non-existent key results in error, so errors must be disabled) to find if lower version is installed
        i = i   1
        sSZ = ""
        sSZ = oShell.RegRead(sRegKey & CStr(i))
        
        If Len(sSZ) > 0 Then
            If sSZ Like "*" & sAddinName & "*" Then
                Debug.Print sSZ
                iIndex = i 'get number at the end of registry key name
            End If
        End If
    Loop Until Len(sSZ) = 0
    
    If iIndex > 0 Then 'previous version installed - overwrite
        oShell.RegWrite sRegKey & CStr(iIndex), sFullPath, "REG_SZ"
    Else 'previous version not found, create new registry key
        oShell.RegWrite sRegKey & CStr(i), sFullPath, "REG_SZ"
    End If
    
    On Error GoTo 0
End Sub