Excel VBA SendKeys not working when trying to Send to Excel Add-In

Question

Intro.: In my company we have a security Add-In installed in Excel, preventing us to save a new Excel workbook without entering the parameters needed.

Challenge: use the SendKeys to send the keys needed to this Excel Add-In.

Problem: when the Add-In screen pops-up (as can be seen in the screen-shot below) the code doesn't seem to continue to this line: SendKeys " ", True.

My Code (relevant part)

Edit 1: The code below is inside a For loop, I am exporting a filterred DB for each user to that user. So everytime I try to save a file for one of the users I will encounter the Add-In (I need to "by-pass" it inside the For loop).

' sort the PM's workbook , hide source data    
Application.DisplayAlerts = False
NewWB.Sheets("Combined").Visible = False
NewWB.Sheets("Sheet3").Delete
NewWB.SaveAs "Budget usage - " & Year(Date) & "-" & Month(Date - 30) & " " & PMList(r)

Dim i    As Long

SendKeys " ", True ' <-- it doesn't get to this line when the Excel Add-In pops up
For i = 1 To 3
    SendKeys "+{DOWN}", True
Next i
SendKeys "{ENTER}", True
For i = 1 To 4
    SendKeys "+", True
Next i
SendKeys "{ENTER}", True

Answer 1

Public Sub TryMe()

    Dim s           As String
    Dim sPath       As String
    Dim sTitle      As String: sTitle = "runme.vbs"

    s = "Set WshShell = WScript.CreateObject(""WScript.Shell"")" & vbNewLine & _
        "WScript.Sleep 6000" & vbNewLine & _
        "WshShell.SendKeys ""+{TAB}""" & vbNewLine & _
        "WshShell.SendKeys ""~"""

    CreateObject("Scripting.FileSystemObject").createtextfile(sTitle).write s
    sPath = "wscript " & ThisWorkbook.Path & "\runme.vbs"
    Shell sPath, vbNormalFocus

End Sub

@Pierre - your idea is to write something like this^^ ?

Then to call it before calling the form and then to delete it somewhere later? Initially I thought that you do not create anywhere the file, but now I have tried it and I noticed. It should work.

Answer 2

It looks as if your addin is sinking the application workbook before save event, so a quick fix to allow saving, would be to sink it again, so in your code, have the following

In a normal module have the following

Public cResink As clsResinkApplication

Have a class called clsResinkApplication and the code for that class will be as follows

Private WithEvents resinkApp As Excel.Application

Public Sub init2(appToResink As Excel.Application)
    Set resinkApp = appToResink
End Sub
Private Sub resinkApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ActiveWorkbook.SaveAs "test.xlsx"
End Sub

And then in the workbook open you can have the following

Private Sub Workbook_Open()

Set cResink = New clsResinkApplication
cResink.init2 Application

End Sub

This should divert to your save sink before the addin's one.