I have a basic WCF client service in the form below. The first time it is called, it returns the correct data from the remote server. However the data changes frequently. On subsequent calls, it returns the same data that it did on the first call. So, Excel appears to return cached data.
If I run the same WCF procedure from Fiddler or a browser, it seems to always return current/refreshed data (so I think the issue is in Excel, not the server).
How can I force Excel VBA to "refresh" the call instead of getting the data from cache?
Note: this will be distributed to a variety of end users, so I cannot make client config changes.
Public Function CallWCF() As String
Dim HttpReq As Object
Set HttpReq = CreateObject("MSXML2.XMLHTTP")
HttpReq.Open "GET", "http://ws.mydomain.com/Rest.svc/getmydata"
Call HttpReq.Send
Do While HttpReq.readyState <> 4
DoEvents
Loop
Dim resp As String
resp = HttpReq.ResponseText
CallWCF = resp
Set HttpReq = Nothing
End Function
I had a similar problem with calling a resource with GET and having a cached response. the way i solved it is calling a "PUT" (or anything else that's not supported) and then call your useful "GET" - that way it'll force a new connection, in term severing the persistence.
From stackoverflow.com/q/11526810/190829, it appears that adding:
httpReq.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
solved the issue on my PC (don't know if it will work on all PCs with different browsers). The other 2:HttpReq.setRequestHeader "Cache-Control", "no-cache"
andHttpReq.setRequestHeader "Pragma", "no-cache"
did not seem to help.I also tried appending the URL with a dummy parameter that changes each request (such as the time or random number) and that also works. However I think it will require changing the server code for all of my operations.