I have an excel file that is supposed to access a remote monitoring server through a web query. Since the data in the file has to be periodically refreshed and saved, I have written a .vbs script to do it. It works fine, but since the server uses basic authentification security, which cannot be turned off, each time it runs, excel throws a popup "Windows Security" window asking for the username and password. There is an option to "Save credentials", but it still requires for the user to click "OK" to proceed, but the system requires there to be no user interaction.
Googling around I found this stack exchange post and other similar approaches, so I modified the script for my needs:
Set wshShell = CreateObject("WScript.Shell")
Do
ret = wshShell.AppActivate("Windows Security")
If ret = True Then
wshShell.SendKeys "{enter}"
Exit Do
End If
WScript.Sleep 500
Loop
The script does trigger when the window appears and even registers an "enter" keypress on whatever window I have focus at that time, but it cannot focus on the popup window itself. It works perfectly fine on other applications such as "Notepad" or "calculator". Is this somehow specific to popup windows? How can I modify the script to focus on the "Excel" popup? Are there other, simpler or more reliable alternatives?
Thanks