Search code examples
wcfrestexcelvba

Excel-VBA REST WCF works on 1st call, but subsequent calls return cached (non-current) data


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

Solution

  • 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" and HttpReq.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.