Excel VBA, I get a prompt "Windows security" when accessing an URL

Question

I Have an Excel file that access an URL HTTPS. The URL as a basic authentication with username and password.

This macro is running every morning and night. It need it to be AUTOMATIC. I have the username and password.

The problem is that each time I run the macro, it Prompt me for the "Windows security". The user name and password is already fill up because I did add this connection in my credential. The "windows security" just wait for a user to click enter. This macro should run automaticly and can't wait for someone to click OK.

I did try VBS Script to login but it only work when user is active on the session. See this : VBA code to pass username & password

I did also try to put the username and password in the URL like : ...

How I connect without the "Windows Security" Prompt???

Here my add 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

Have you tried to use "http" instead of "https" on your url? — aprados
What do you mean by "I did try VBS Script to login but it only work when user is active on the session"? Can you explain this more... — TheSilkCode

jsscio jsscio · Accepted Answer · 2017-05-04T14:50:16

If you can't control the security level of the remote machine, then I would go for one of the following solutions:

Use SendKeys to send the Enter key, or equivalent keystroke that will 'click' the button for you. Use a timer to wait 2-3 seconds after you authenticate to then send the Enter key so it won't send Enter before the msgbox is displayed.
Use Windows API to get the handle for the security message popup, you can then control it programmatically.

Excel VBA, I get a prompt "Windows security" when accessing an URL

2 Answers