Enable Excel COM Add-in using VBA without using .C

问题:

I have the following code, which works fine, but only if the user is viewing the screen. I need to find a solution that achieves the same outcome, but works when the screen is locked. I know SendKeys does not function when the screen is locked.

I can't use vntAddIn.Connect = True because this results in an error. The COM add-in can be successfully enabled manually using the dialog box, but not via the .Connect property.

I believe I may be able to use the SendMessage or PostMessage API to achieve this but I'm not sure how to do this. I'm not sure which window handle to use (is it the main Excel application for example?) and I don't know how to send Alt combinations? Any assistance would be appreciated.

Note, I'm aware that using PAGE DOWN to get to the bottom of the list is not robust as it may fail if the Com Add-In is not the last item on the list. I have a separate solution to identify where in the list the Add-In appears and can replace the PAGE DOWN with a specific number of DOWN arrows to fix this, but before implementing this, I want to make sure it's possible at all, as without the ability to do this when the screen is locked, there's no point.

Public Sub EnableComAddIn()
  Dim blnAddInIsConnected  As Boolean
  Dim vntAddIn As Variant
  Const CstrComAddinDescription As String = "Oracle Smart View for Office"

  blnAddInIsConnected = False
  For Each vntAddIn In ThisWorkbook.Application.COMAddIns
    If vntAddIn.Description = CstrComAddinDescription Then
      blnAddInIsConnected = vntAddIn.Connect
      Exit For
    End If
  Next

  If Not blnAddInIsConnected Then

    'Make sure Excel application window is active, and a cell is selected.
    ThisWorkbook.Activate
    ThisWorkbook.Worksheets(1).Select
    ThisWorkbook.Worksheets(1).Cells(1, 1).Select

    'Make sure Developer tab is shown on ribbon or Alt-L will not work.
    ThisWorkbook.Application.ShowDevTools = True

    'Use SendKeys to use ALT-L, J to open Com Add-ins window, page down twice to select last entry on list, press space to check the box, press Enter to save.
    DoEvents
    ThisWorkbook.Application.Wait Now() + TimeSerial(0, 0, 1)
    DoEvents
    ThisWorkbook.Application.SendKeys "%LJ{PGDN}{PGDN} {ENTER}", True
    DoEvents
    ThisWorkbook.Application.Wait Now() + TimeSerial(0, 0, 1)
    DoEvents

  End If
End Sub