3

I need to enable COM addins through VBA. The addins already exists under COM addins, but become unchecked when Excel crashes.

Sub hyp()
    Dim objAddIn As Object
    For i = 1 To Application.COMAddIns.Count

        Set objAddIn = Application.COMAddIns.Item(i)
        On Error Resume Next
        If Application.COMAddIns.Item(i).Description = "Oracle Smart View for Office" Then
            'MsgBox Application.COMAddIns.Item(i).Description
            'NEED TO ENABLE THE COM ADDIN

        Else
        End If
    Next i
End Sub
Community
  • 1
  • 1
PerlBatch
  • 200
  • 2
  • 2
  • 10

3 Answers3

7
Public Sub Connect_COM_AddIn(Name As String)

    Dim ndx As Integer

    For ndx = 1 To Application.COMAddIns.Count
        If Application.COMAddIns(ndx).Description = Name Then
            Application.COMAddIns(ndx).Connect = True
            Exit For
        End If
    Next
End Sub
Excel Developers
  • 2,785
  • 2
  • 21
  • 35
  • Note that the Description property is case sensitive. – Excel Developers Feb 01 '18 at 15:11
  • 2
    Thank you.. I tried the same, but am getting an error "This add-in is installed for all users on this computer and can only be connected or disconnected by an administrator". When i googled it, it says the application should be run as administrator. Even after that, it neither gave me an error nor worked. I am thinking an other way round. Is there a way to pop up the COM addins windows, so that i manually check the addin box, i want. Here is the code which worked for Addins. (Not for COM addin) Application.CommandBars(1).FindControl(ID:=943, recursive:=True).Execute – PerlBatch Feb 02 '18 at 07:02
  • I've added a new question as I'm having a problem, which is related to the comment by @PerlBatch, but isn't solved by the accepted answer of this question: https://stackoverflow.com/questions/53338022/enable-excel-com-add-in-using-vba-without-using-connect-but-instead-using-sendm – M1chael Nov 16 '18 at 13:10
  • I haven't tested this, but can't you index the add in by name instead of looping through each add in and testing against the Name variable? – Colm Bhandal Feb 06 '19 at 10:21
  • 1
    @ColmBhandal: No. – Excel Developers Jun 19 '19 at 07:24
2

Note: Please see the comment of BigBen below - this approach may not always work as the indexer does not always coincide with the description. If you need to search by description, then the Excel Developers answer is probably applicable (though I haven't personally tried it or needed it).

A simpler alternative to the answer of Excel Developers that worked for me is to index the com add in directly by its string name instead of looping through the com add ins using an integer index and comparing to the description. In particular, this code worked for me (I've included a connect and disconnect version):

Public Sub Connect_COM_AddIn(Name As String)
    Application.COMAddIns(Name).Connect = True
End Sub

Public Sub Disconnect_COM_AddIn(Name As String)
    Application.COMAddIns(Name).Connect = False
End Sub
Colm Bhandal
  • 3,343
  • 2
  • 18
  • 29
  • 1
    Just a note - from the [`documentation`](https://learn.microsoft.com/en-us/office/vba/api/office.comaddins) it looks like you can use the *index* or *ProgID* with the `COMAddIns` collection - but this is not the same as the description, i.e. "Oracle Smart View for Office" in OP. In other words, `Application.COMAddIns("Oracle Smart View for Office").Connect` would throw a subscript out of range. – BigBen Oct 09 '19 at 19:05
  • 1
    Thanks BigBen, I'll add to the answer. – Colm Bhandal Oct 10 '19 at 10:03
0

I have the same system block mentioned earlier where system privileges won't allow me to use Application.COMAddIns(Name).Connect = True. This is a bit of a workaround, but to get the COM add ins box to pop up you can use SendKeys to pull it up. Keep in mind that SendKeys only excutes at the end of a run on Excel 2010 onwards, so to get it working correctly you would need to check if the user is connected to the add-in first thing. If so, call another sub; if not use SendKeys to get the dialog open and end the sub. These are the keystrokes that worked for me, there may need to be some edits depending on how many options are in your menus.

Sub test()

'Checks if COM is installed and active
comFound = False
comRibbon = True
For i = 1 To Application.COMAddIns.Count
    If Application.COMAddIns(i).Description = "NAME" Then
        comFound = True
        If Application.COMAddIns(i).Connect = False Then
            comRibbon = False
        End If
        Exit For
    End If
Next i

'Exits sub if not installed
If comFound = False Then
    MsgBox ("You do not have NAME installed.")
    Exit Sub
End If

'Directs user to rest of code if active, otherwise opens dialog
If comRibbon = True Then
    Call test2
Else
    MsgBox ("Please select NAME in the following dialog before rerunning.")
End If

SendKeys "%FT{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{TAB}{TAB}{TAB}{DOWN}{DOWN}{TAB}~", True

End Sub

Sub test2()
    'Rest of code
End Sub
canoninsea
  • 1