1

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
braX
  • 11,506
  • 5
  • 20
  • 33
M1chael
  • 251
  • 1
  • 9
  • 1
    Possible duplicate of [Enable COM addins in Excel through VBA](https://stackoverflow.com/q/48565694/11683) – GSerg Nov 16 '18 at 12:46
  • @GSerg This is NOT a duplicate of the question you asked. The accepted answer of that question is not applicable in this case; the second paragraph of my question references this fact. The comment by PerlBatch in reply to the linked question's accepted answer appears to be a similar issue to my own, but that comment has had no replies. – M1chael Nov 16 '18 at 12:59
  • That question does not have an accepted answer, only an upvoted one. It is a perfect duplicate otherwise: it is not only about the same issue (enabling a COM add-in), it is about enabling the very same COM add-in ("Oracle Smart View for Office"). You are not expected to create a new question about the very same problem when an already existing question does not contain a solution - simply because it would be nothing more than a "me too" comment, and these are removed. – GSerg Nov 16 '18 at 13:24
  • It is perfectly okay to open a new question when there is an *accepted* answer that works for others and does not work for you because you have slightly different conditions which you can mention in comparison to the existing question, but that does not seem to be the case. – GSerg Nov 16 '18 at 13:24

0 Answers0