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.
|