VBA macro not able to call API second time

Question

I am currently making a vba excel macro which calls an API and fill the data from it into the excel sheet, but I am facing an issue where I can only call the API through macro once, and after that it somehow caches the response and not call the API again when we re run the macro. I have to restart the whole excel for it to start working again and it only allows me to call it once then too and again caches the data. Here is my function:

Sub FetchDataFromAPI()
    Dim xhr As Object
    Dim jsonResponse As String
    Dim parsed As Object
    Dim schedules As Object
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim i As Long
    
    ' Create a new XML HTTP request object
    Set xhr = CreateObject("MSXML2.XMLHTTP")
    
    ' Open the request and specify the API endpoint URL
    xhr.Open "GET", "http://127.0.0.1:8000/data/", False
    
    ' Send the request
    xhr.send
    
    ' Check if the request was successful (status code 200)
    If xhr.Status = 200 Then
        jsonResponse = xhr.responseText
        
        
        ' Parse the JSON response to extract schedules
        Set parsed = JsonConverter.ParseJson(jsonResponse)
        Set schedules = parsed("schedules")
        
        ' Set the target worksheet
        Set ws = ActiveSheet
        
        ' Clear existing data in the sheet starting from row 2
        ws.Cells(2, 1).Resize(ws.Rows.Count - 1, 11).ClearContents
        
        ' Insert data into Excel sheet starting from row 2
        rowNum = 2
        
        ' Iterate over the schedules list and insert into Excel
        For i = 1 To schedules.Count

            ' Fill data into different columns
            ws.Cells(rowNum, 1).Value = schedules(i)("schedule")
            
            rowNum = rowNum + 1 ' Move to the next row
        Next i
        
        MsgBox "Data fetched and inserted successfully!", vbInformation
    Else
        MsgBox "Failed to fetch data. Status code: " & xhr.Status, vbExclamation
    End If
    
    
    ' Clean up
    Set xhr = Nothing
End Sub

Is there anything missing in my code which is resulting into my API not being called second time, or is there any setting which is resulting into data caching? I need to continously update both my API and the macro at the same time and this is causing delay in it. Also I want to be sure that this doesn't occur on my client's device when they try to call macro, as the macro does not throw any error on second call and just uses cached data, they won't be able to see if the data is actually fetched or not.

Answer 1

It's a known issue with MSXML2.XMLHTTP that it is caching data, see for example the discussion here How to make Microsoft XmlHttpRequest honor cache control directive

One workaround is to change the URL for every call by adding a parameter that changes every time, eg by adding a timestamp. Now every time you call the API, the URL will be different and therefore no cached version can be found.

Check if this is working for you:

 xhr.Open "GET", "http://127.0.0.1:8000/data/p=" & replace(now, " ", "_")