0

I have been struggling to make this work. I have a VBA code that tries to get data via REST API. It does get data correctly when it is run for the first time but after the second time it does not get the updated data but gets the same data as in the first call. This issue is replicable every time I call the GET call multiple times.

My VBA code is as following ("Second run" above means running Main for the second time):

Sub Main()
    
    Dim MyHtml As String
    Dim MyXmlResponse As String

    MyHtml = "..."
    
    MyXmlResponse = DoRestCall(MyHtml, "GET") ' Result in the second or later run is always the same as in the first run even the data is updated

End Sub


Function DoRestCall(Html As String, RestCallAction)

    Dim XmlHttp As Object
    Dim XmlHttpResponse As String

    Set XmlHttp = CreateObject("MSXML2.XMLHTTP")
    Call XmlHttp.Open(RestCallAction, Html, False)
    
    XmlHttp.setRequestHeader "Authorization", "Basic " + _
        "EncodedCredntial"

    XmlHttp.setRequestHeader "Content-Type", "application/json"
    
    Call XmlHttp.Send
    
    XmlHttpResponse = XmlHttp.responseText
    DoRestCall = XmlHttpResponse
    
    Set XmlHttp = Nothing
    
End Function

Would somebody kindly advise what I am missing? Please let me know if more information/clarification is needed.

Thanks & Regards, Kyoto

Kyoto
  • 37
  • 5
  • Did you try a API development tool like https://www.postman.com? This way you can see if it an issue with the API itself or an issue with your code/library. – Pᴇʜ Jun 18 '21 at 09:26
  • Thank you for your quick response. Yes, I confirmed that the issue does not happen when I tried with Postman. GET call with Postman with always returns the latest updated data. So there must be something wrong with my VBA code. – Kyoto Jun 18 '21 at 09:52
  • Actually the code looks good to me. This should get the updated data into `MyXmlResponse` – Pᴇʜ Jun 18 '21 at 09:58
  • Thanks for taking a look at the code. I see. I cannot find anything wrong with my code either so far. It just does not get the updated data somehow. – Kyoto Jun 18 '21 at 10:07
  • you are likely being served cached results. Try adding a random number to end of url or see https://stackoverflow.com/questions/62418524/session-id-not-refreshing-in-api-call/62450467#62450467 If that sorts it I will close as duplicate. – QHarr Jun 18 '21 at 12:53
  • 1
    Hi @QHarr , thank you for taking a look into this. It makes sense that I am likely being served cached results. I wanted to try the methods you suggested as soon as possible but the target API site is currently not accessible probably due to some maintenance. I will report the result to you as soon as possible. Thanks! – Kyoto Jun 19 '21 at 13:11
  • Hi @QHarr . Yes, adding .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT" worked for me too. Please kindly close my question as duplicate. Do you happen to know any Microsoft document or any other document that explains why this will delete the REST query cache? Thanks & Regards. – Kyoto Jun 20 '21 at 10:04
  • I don't believe it deletes. It is simply an instruction to the server which the server can ignore depending on config. – QHarr Jun 20 '21 at 10:11

0 Answers0