5

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
E-Rock
  • 180
  • 7
  • 1
    oh, the phenomenon of electrospiritsm, when things work when they should not and do not work when they should – Lodi Aug 17 '18 at 16:26
  • Is there anything else going on in the workbook on workbook open? Any events? – QHarr Aug 17 '18 at 16:49
  • 1
    It sounds like a timeout issue similar to this [Providing authentication info via msxml2.ServerXMLHTTP](https://stackoverflow.com/questions/20712635/providing-authentication-info-via-msxml2-serverxmlhttp) – Profex Aug 17 '18 at 17:21
  • Or better yet [XmlHttp Request Basic Authentication Issue](https://stackoverflow.com/questions/1358550/xmlhttp-request-basic-authentication-issue) – Profex Aug 17 '18 at 17:23
  • No events on workbook open, I'm calling these subs directly when I need them. @Profex those issues don't appear to be related. Authentication occurs correctly every time I call this sub EXCEPT for the first time I run it on a given day, and it doesn't time out necessarily. It opens the native Excel login modal to allow me to log in to the API service, and this all works as well and occurs promptly with no timeouts. If I close the login modal without logging in and run the sub a second time, authorization occurs correctly and all expected data is returned consistently. – E-Rock Aug 17 '18 at 17:30

1 Answers1

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.

Dylan L
  • 136
  • 2
  • 6
  • Accepting this answer as it's a decent solution and this is a pretty slow topic. I've moved on to a new employer and will be unable to test or implement any other solutions – E-Rock Sep 11 '18 at 15:30