Excel-VBA REST WCF works on 1st call, but subseque

2019-07-27 08:23发布

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

2条回答
ら.Afraid
2楼-- · 2019-07-27 08:31

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.

查看更多
ゆ 、 Hurt°
3楼-- · 2019-07-27 08:40

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.

查看更多
登录 后发表回答