Excel VBA Authorization fails on first run, runs fine the second run

Question

I have a Sub in an Excel workbook that accesses an API.

On the first run, a login modal pops up as if the Authorization header hadn't been sent.

If I close that without logging in and run the sub a second time, authorization is successful and my data is returned.

This only occurs the first time I open the workbook for the day. The authorization is successful the next time I open the workbook. As far as I can tell, my code is correct. Any suggestions?

Dim hReq As Object
Set hReq = CreateObject("MSXML2.XMLHTTP")
hReq.Open "GET", APIURL, False
hReq.setRequestHeader "Authorization", "BASIC " & Base64Encoding(APIKEY & ":xxx")
hReq.Send

Answer 1

Have you looked into adding some error handling?

Realize that won't answer why it's not working on the first try. However it may solve your problem of having to rerun the code manually.

For example, you can use the .status method on your hReq object:

If hReq.Status <> "200" Then
    Status_Request = hReq.Status
    Application.Wait Now + 0:00:01

From there you can can add a means to retry the request. You could in theory go with error an error handling breaking point, i.e.:

On Error GoTo ErrorHandler

Where you would put the ErrorHandler: title higher up in your code.

Or you could do a Do Untill loop on the Status_Request variable.

Counter = 0
Do Until hReq.Status<> "200"
    Counter = Counter + 1
    {Your code here}
    If Counter = 4 Then
        MsgBox("Reached the retry limit, trys: " & Counter)
        Exit Sub
    End If

To prevent the code from running into an infinite loop add a counter and set a condition that ends the loop after the counter reaches x number of retry's.