How to click Enter while Excel is waiting for user action?

Question

When I save my Excel file with VBA, it asks me to enter my credentials (it saves it on some kind of server). Looking though the website I found a way to use SendKeys and delay the action.

My issue:
The code stops and waits for user input (Enter) and I can't find a way to start the next line of code (that is actually the action of pushing Enter...)

My code:
Here is the specific part I need help with:

.SaveAs Filename:="Y:\Must\Must Chart Management.csv", FileFormat:=xlCSV, Local:=True
Application.Wait (Now + TimeValue("0:00:05"))
SendKeys ("{ENTER}")

Here is the whole thing:

Sub UpdateMust2020()
    
Application.DisplayAlerts = False
KillFile = "B:\Must\Must Chart Management.csv"

With ActiveWorkbook
    
    Worksheets("Must Chart Management").Activate
        .SaveAs Filename:="Y:\Must\Must Chart Management.csv", FileFormat:=xlCSV, Local:=True
        Application.Wait (Now + TimeValue("0:00:05"))
        SendKeys ("{ENTER}")
        .SaveAs Filename:="E:\users\Must Definition Dashboard\Convergence\Must Chart Management.csv", FileFormat:=xlCSV, Local:=True
        
    Worksheets("MasterQuery").Activate
        .SaveAs Filename:="Y:\Must\Must Statistics Charts.csv", FileFormat:=xlCSV, Local:=True
        .SaveAs Filename:="E:\users\Must Definition Dashboard\Convergence\Must Statistics Charts.csv", FileFormat:=xlCSV, Local:=True
        
    Worksheets("Csv").Activate
        .SaveAs Filename:="Y:\Must\MustWin2020-template.csv", FileFormat:=xlCSV, Local:=True
        .SaveAs Filename:="E:\users\Must Definition Dashboard\Convergence\MustWin2020-template.csv", FileFormat:=xlCSV, Local:=True
        
    Worksheets("Csv for One Industry ALL VIEW").Activate
        .SaveAs Filename:="Y:\Must\Csv for One Industry ALL VIEW.csv", FileFormat:=xlCSV, Local:=True
        .SaveAs Filename:="E:\users\Must Definition Dashboard\Convergence\Csv for One Industry ALL VIEW.csv", FileFormat:=xlCSV, Local:=True
        .Close False
    
    End With
    Application.DisplayAlerts = True
    
End Sub

And here is the screenshot:

Followthrough

My guess is you are sending ENTER for a numeric keypad and you do not have one. Application.SendKeys("{ENTER}") is for the numeric keypad, but Application.SendKeys("~") if for normal enter. Read more here. – Signe

Also, a side note, if you are using Application.SendKeys() method, make sure to activate the window you are wanting to send the button too. – Signe

You are right, the issue is that code execution stops and Application object become inresponsive while user is in opened dialog or editing the cell content. To get control over application in that case you can start another separate process prior to SaveAs method call, e. g. mshta.exe, and execute a neccessary VB code from that process. Take a look at this answer to get the approach. – Algo

@DeanDeVilliers Thanks for your reply, I tried and still have the issue sadly. Thanks for taking your time though ! – Followthrough

@Algo Thanks for the complete answer! I'm having trouble to apply it though, it seems that it is far beyond my skill levels. Should I be using the whole code? I'm not sure where to start. Thanks for your help – Followthrough

@Followthrough could you please clarify, are the credentials in the dialog is autofilled, so that you need just to press Enter to close the dialog? Some screenshots might be helpfull. Please edit the question and insert one if you have. – Algo

@omegastripes, here you go, I added a screenshot. It is indeed autofilled, and I just need to press enter. Thanks for your help – Followthrough

The screenshot looks like a Windows popup, thus your focus on an Excel sheet won't work. You need an Windows API to focus on that popup. – Bergwall

Answer 1

Research found this :

SendKeys "~", True

Stich