Excel VBA, I get a Windows Security prompt when accessing the url

Question

I have an Excel file that accesses HTTPS URLs. URL as basic authentication with username and password.

This macro works every morning and night. It must be AUTOMATIC. I have a username and password.

The problem is that every time I run a macro, it asks me for "Windows security." The username and password are already filled in because I added this connection to my credentials. Windows Security just waits for the user to press the Enter button. This macro should run automatically and cannot wait for someone to click OK.

I tried VBS Script to log in, but it only works when the user is active in the session. See: VBA code for passing username and password

I also tried putting the username and password in the url, for example: ...

How can I connect without prompting Windows Security?

Here is my added connection function:

Function GetForcast(DateStart As String, DateEnd As String) Sheets("Forecast RAW").Select With Sheets("Forecast RAW").QueryTables.Add(Connection:= _ "URL;https://weather.dtn.com/basic/rest-3.4/obsfcst.wsgi?dataTypeMode=0001&dataType=HourlyForecast&startDate='" & DateStart & "'T00:00:00Z&EndDate='" & DateEnd & "'T00:00:00Z&stationID=KILG" _ , Destination:=range("$A$1")) .Name = "00Z&stationID=KILG" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False 'Shell "WScript C:\Temp\Logon.vbs", vbNormalFocus .Refresh BackgroundQuery:=False End With End Function 

Answer 1

If you cannot control the security level of the remote machine, I would use one of the following solutions:

• Use SendKeys to send the Enter key or equivalent key press, which will be the 'click' for you. Use the timer to wait 2-3 seconds after authentication, and then send the Enter key so that it does not send Enter before displaying msgbox.

• Use the Windows API to get the security popup handle, then you can control it programmatically.