EXCEL VBA Caching GET request?
 

I have developed a simple PHP CRUD Rest API with Excel interfacing as the front end UI.

When spreadsheet loads opens a form which fires off a GET request using the following method:

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://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"


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

Which works great gets the data and displays on form.

Via another routine i can add a new company record. and verify that i can see it on the web server.

However, when i next fire the GET method. It does not return the new record. I have placed some debugging on the Server and it only registers the initial call. any subsequent calls arent being logged.

If i close Excel down and reopen i get the new record. I destroy all the variables after use. I am not sure where to go next? ANy pointers greatfullly received.

EXCEL VBA Caching GET request?
 

On 12/07/2019 09:15, armsiee wrote:

<snip


If i close Excel down and reopen i get the new record. I destroy all the variables after use. I am not sure where to go next? ANy pointers greatfullly received.


The response from your URL is being cached somewhere in MSXML2.XMLHTTP60

Put a random parameter like a timestamp to the end of the URL

i.e.

sURL="http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&t=201902022 00423"


--
Adrian C

EXCEL VBA Caching GET request?
 

On Friday, 12 July 2019 19:01:58 UTC+1, Adrian Caspersz wrote:
On 12/07/2019 09:15, armsiee wrote:

<snip


If i close Excel down and reopen i get the new record. I destroy all the variables after use. I am not sure where to go next? ANy pointers greatfullly received.


The response from your URL is being cached somewhere in MSXML2.XMLHTTP60

Put a random parameter like a timestamp to the end of the URL

i.e.

sURL="http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&t=201902022 00423"


--
Adrian C

worked a treat! thanks Adrian.