XMLHTTP Caching: Xml is bringing the same data after the first request Excel VBA

Question

I'm reading an XML feed using XMLHTTP. I noticed that it reads fine the first time when I open the Excel file but, when the XML changes and I run the macro again, it brings in the same previous data. I did some research about how to stop caching. I read that adding a random number as an extra parameter will fix it but didn't work for me.

Any ideas?

Sub MLB_PinnyParser()

Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Req.Open "GET", "http://xml.pinnaclesports.com/pinnaclefeed.aspx?sporttype=Baseball&sportsubtype=MLB", False
Req.send
Resp.LoadXML Req.responseText

 For Each Event In Resp.getElementsByTagName("event")

'More code here

 Next Event

Set Req = Nothing
Set Resp = Nothing

End Sub

Answer 1

You can often overcome this by modifying your URL with each request. Just add a random number as a querystring param. For example:

' Seed the RNG somewhere at the start of your app...
Randomize

...

Sub MLB_PinnyParser()

    ' Generate a random, six-digit number...
    Dim intRand As Long
    intRand = Int((900000) * Rnd) + 100000

    ' Add the number as a param to the request...
    Dim strUrl As String
    strUrl = "http://xml.pinnaclesports.com/pinnaclefeed.aspx?sporttype=Baseball&sportsubtype=MLB" & "&blah=" & intRand

    Dim Req As New XMLHTTP
    Req.Open "GET", strUrl, False
    ...

End Sub

Since it sounds like you've tried this already, try adding a couple caching-related headers to your request to see if that makes a difference:

Req.SetRequestHeader "Cache-Co­ntrol", "no-cache,max-age=0"
Req.SetRequestHeader "pragma", "no-cache"