Excel GET Request Caching?

Question

I have a simple PHI restful service to perform CRUD actions om a MYSQL database, with an Excel workbook acting as the UI.

Opening the workbook fires a GET request to the api which works fine and the current data is displayed on the User Form. I have then controls which allow the user to update and add new records. Which return responses from the Server and i can see that the database has updated.

The issue is that when i fire subsequent GET request I only get the original dataset without any of the changes to the underlying data.

It is only when I close Excel and re-open the Workbook that i get the new data returned to me?

I have ensured that all variables content relating to the API call are destroyed after use and the variable containing the data is set to nothing before the call so that the form cannot be populated with 'old' data.

I have also placed debugging messages on the server side and it only registers the intial GET. Despite stepping through the request on the VBA side in debugger it does not appear that the GET request reaches my server.

I have also tested the Server by calling the page in the browser and i can see the JSON with my new data in it.

Sub getData() 

Dim ws As Worksheet 
Dim jsonText As String, sUrl As String, response As String 
Dim xmlHttp As New MSXML2.XMLHTTP60 
Dim JSON As Dictionary 
Dim i As Long 
Dim Item As Object 
Dim comp As Dictionary 

Set var = Nothing 
Set JSON = Nothing 
response = "" 

sUrl = "http://xxxxxxxxxxxxxxxxxxxxxxxx" 


With xmlHttp 
    .Open "GET", sUrl, False 
    .Send 
End With 

response = xmlHttp.ResponseText 
Debug.Print response 
Set JSON = JsonConverter.ParseJson(response) 
ReDim var(JSON("records").Count, 5) 
i = 0 
For Each comp In JSON("records") 
    var(i, 0) = comp("CompId") 
    var(i, 1) = comp("CompName") 
    var(i, 2) = comp("Address1") 
    var(i, 3) = comp("Address2") 
    var(i, 4) = comp("Address3") 
    var(i, 5) = comp("PostCode") 

    i = i + 1 
Next 

Set xmlHttp = Nothing 

Debug.Print "finished" 

End Sub

No error messages. Everything appears to be functioning apart from the fact that the new data is not returned until i re-open Excel.

Any pointers gratefully received. Code edited so as to appear in proper way

Thanks Yasser for the edit. Link to what the api? I can't do that as that has customer data. The api returns a JSON string containing the data as I stated if i call the api in the browser I can see the new data in the JSON string. — Armsiee, Jul 12 '19 at 08:35
I don't see any lines that refer you have populated the array to the worksheet. Can post sample of the JSON output? — YasserKhalil, Jul 12 '19 at 08:41
Data is not posted to the spreadsheet, the contents of var which is defined as a public object and is set as the source of a list box on the userform. I have no problem with the display of the data itself the issue is that the second GET call returns the same data set as the first even though in the meantime I have updated the underlying database with new data. — Armsiee, Jul 12 '19 at 09:22
You have used `Set var = Nothing ` and I think this is good. Try `response=Empty` — YasserKhalil, Jul 12 '19 at 09:33
Thank you i will try that but as a procedural variable I can't see how it will be retaining its contents in between separate runs? My feel is that the issue has to be around the http object. Have I declared it correctly xmlHttp As New MSXML2.XMLHTTP60 I have seen other examples using late binding. — Armsiee, Jul 12 '19 at 09:37
Have you tried `Erase var` at the end of the code as this is array — YasserKhalil, Jul 12 '19 at 09:38
Without reading fully if suspect caching try my answer here https://stackoverflow.com/a/52757818/6241235 — QHarr, Jul 12 '19 at 09:41
thanks QHarr, that looks promising ... I'll shall give it a go later and report back. — Armsiee, Jul 12 '19 at 09:50
both methods worked. thank you very much QHarr! went with the RequestHeader as felt a bit more elegant! — Armsiee, Jul 12 '19 at 19:06

Excel GET Request Caching?

0 Answers0