Alternate to SendKeys while saving file

[DMBLuva]

So, I'm not even sure how to approach this issue, but on a hunch I decided to try sendkeys hoping it would help. So far...no. So here's my issue. I created a database that sends auto-emails and at the same time saves an Excel workbook based on filtered information. The problem is, when I go to save, the company I work for puts a security box in play that asks me to verify I'm the author (defaults my name in the text box) and gives me four options as to what type of doc it is so it knows how to encrypt it (this is also defaulted). With the push of enter it's gone and the code can continue to save as normal. Emails sent by my Access DB will be in the hundreds at some point and it would be crucial to get this working without interaction.
So my first attempt to bypass this was to use the sendkeys command (I know not ideal), but all it does is adds a line under my code, it's not sending the key to the active window. Another complication is the security window is not triggered until the actual save code is implemented. I tried to pause the actual sendkey function until after that save line is run, but I don't think I'm doing that right.

Anyone have any suggestions on a better way to do this or perhaps just some help on cleaning up what I have? My theory was to activate the sendkeys command with pause allowing the save to happen and bam the timing with be enough for the enter key to fire...but hey doesn't it always seem like things go better in your head?

Here is the code I'm working with. Any suggestions are welcome, thank you in advance.

'if false then save is false and only for viewing
If bOpen = False Then
    Call mSendKey
    objCreateWb.SaveAs FileName:=strFiledirectory
    objCreateWb.Close
End If

Public Sub mSendKey()
Dim EnterKey As String

EnterKey = "~"
SendKeys EnterKey, True
Call Pause(1)

End Sub

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = Timer
    Do While Timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

 

[lameid]

I would suggest that either what you are doing is not allowed by your company or you are working on the exception to the rule. Assuming the latter, I would pursue understanding exactly how the dialog comes up... If it is an Excel macro, you can probably replicate whatever it is doing and disable it. My expectation is that looking into the security program will be easier than working around it.

I don't think sendkeys will work with another app. You'd have to activate the other application and emulate keyboard keystrokes... You can probably find ways to do those things but you may also need another language to get going (like C#.Net).

A side note, unless I missed it being deprecated, the Sleep procedure pauses execution for a number of milliseconds passed to it.

 

[DMBLuva]

Thank you so much for your suggestion! Just so you know I'm on the up and up, my supervisor offhandedly gave me this challenge and said she was going to be interested to see how I crack it. Fun right?
Ok so I've looked at the possibilty that it is a macro and found that it is actually an add on to Microsoft Office. It says specifcally it is a COM add on. I suppose I will do some checking, but if anyone has an idea, I would be grateful.

Thanks again for your help!

 

[lameid]

I'm by far not an Excel expert (You caught my attetion with sendkeys as I know Access). Mostly I wanted to let you know that I can't help more but I am curious as how you work around it. I would think you could look into using VBA to turn off add-in's or how to start Excel without them... You might need to start a new thread for that specific purpose to draw appropriate attention (0 replies).

 

[DMBLuva]

Well with your help, I was able to find a way to enable and disable it upon saving. That fixed it.
Thanks again!

'------------------------------------
   'find Com ProgramID
   ' Dim lngRow As Long, objCOMAddin As COMAddIn
    
    'lngRow = 1
'With objExcel.ActiveSheet
      'For Each objCOMAddin In objExcel.COMAddIns
         '.Cells(lngRow, "A").Value = objCOMAddin.Description
         '.Cells(lngRow, "B").Value = objCOMAddin.Connect
         '.Cells(lngRow, "C").Value = objCOMAddin.progID
         'lngRow = lngRow + 1
      'Next objCOMAddin
'End With

'-------------------------------------------------------

'disable
If objExcel.COMAddIns("ProgramID Here").Connect = True Then
    objExcel.COMAddIns("ProgramID Here").Connect = False
End If