Disabling COM addin with VBA

[petereddy]

Hi,

Is there a way, using VBA, to check whether a COM addin is active in Excel, and if it is, to deactivate it? Thanks.

Peter

 

[ZVI]

Hi,
Try this:

Sub DisableComAddIns()
  Dim objComAddIn As COMAddIn
  For Each objComAddIn In Application.COMAddIns
    If objComAddIn.Connect Then objComAddIn.Connect = False
  Next
End Sub

 

[petereddy]

Hi ZVI,

Thanks for responding. This solution appears to attempt to uninstall the addins, where I only want to untick them and leave them in my list. One of the motivations for not wanting to uninstall the addin is that my group policy won't allow it.

Any thoughts on how to do this? Thanks.

Peter

 

[ZVI]

Peter, code does not uninstall COM Add-Ins, but tries to untick (disconnect) them without deleting from the list.

If COM Add-In was registered in the HKEY_CURRENT_USER part of the computer registry, then it can be disconnected via code for the current user.
Changes are applied to the current and new sessions of Excel.

But if COM Add-In was registered with administrative privileges in the HKEY_LOCAL_USER part of the registry, then it is installed for all users of the computer (current, admin ...).
In this case user without administrative privileges can only untick it manually for current excel session.
But code faults with error message: “This add-in is installed for all users on this computer and can only be connected or disconnected by an administrator”.

If user has administrative privileges then code works for the current session of Excel.
In fact, administrative privileges allow user code to modify the registry in the HKEY_LOCAL_USER part too for applying changes to new Excel sessions.

 

[petereddy]

"

In this case user without administrative privileges can only untick it manually for current excel session.​

"

I'm disappointed to hear that, but am happy to learn it. Thanks.

 

[petereddy]

The registry path for the addin is something like Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\MyAddin and I'm able to change the value of the LoadBehavior value.

I know this script would be run outside Excel, but it's still related to Excel, so perhaps it's still relevant to this forum. Is there a VB script that could check the LoadBehavior

value ​

for this addin, and if it's not zero, set it to zero?

Thanks.

 

[ZVI]

You may play with this VBA code:

Sub DisableComAddIns()
  Dim objComAddIn As COMAddIn, sRegKey As String, IsReloadMsg As Boolean
  On Error Resume Next
  With CreateObject("WScript.Shell")
    For Each objComAddIn In Application.COMAddIns
      If objComAddIn.Connect Then
        sRegKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\AddIns\" & objComAddIn.progID & "\LoadBehavior"
        Err.Clear
        objComAddIn.Connect = False
        If Err Then
          IsReloadMsg = True
          .RegWrite sRegKey, 2, "REG_DWORD"
        End If
      End If
    Next
  End With
  If IsReloadMsg Then
    MsgBox "Reload Excel to apply COM Add-Ins disconnecting"
  Else
    MsgBox "COM Add-Ins have been disconnected"
  End If
End Sub

 

[Trevor G]

You might do this with a Batch File or Power Shell script to keep it outside of Excel.

Chip Pearson has written extensive code for API calling functions within Excel

Registry Functions

Microsoft also has done this.

https://support.microsoft.com/en-gb/help/310516/how-to-add-modify-or-delete-registry-subkeys-and-values-by-using-a-reg

I've added this in just as Vladimir has posted his code. These links still might be useful to you.

 

[petereddy]

Thanks for your generous help, Vladimir and Trevor.

 

[ZVI]

You are welcome Peter. Even if there is no good solution, we are now closer to understanding that it is a fact ...