MSXML2.XMLHTTP refresh 1

[osx99]

I have some code which calls a function to retrieve a webpage source as a string

call

Private Sub Worksheet_Change(ByVal Target As Range)
    If raceURL2 <> "" Then
        liveshowoutput = ExecuteWebRequest(raceURL2)

        resultArray = Split(liveshowoutput, "HORSE_NAME -->")
End Sub

Function

Public Function ExecuteWebRequest(url As String) As String

    Dim oXHTTP As Object
    
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing
 'Debug.Print (ExecuteWebRequest)
End Function

All is ok when this is first run but when the website updates my code doesn't. The site updates approx every 20 seconds for 10 minutes. I can see the webpage update in a seperate browser but the vba code acts as if the original call to the webpage has been cached. If I stop the vba shut down excel and restart it then picks up latest data but doesn't refresh again.....?

Is there something obvious I'm missing here and is there a way to force the web request to retrieve latest data?

 

[tsuji]

A commonly used method---that one can loosely think of it as cache busting---to overcome the caching problem is to attach a random string to the url's query string. Let's say using timer()*100 with an arbitray query variable named "cb". Just be careful that cb is exotic enough not to collide with any application's query variable names in most circumstances. (At the limit, you can even spare the "cb=" part and attach just the timer()*100---or a random string---to the url, although it makes some obscurity out of the url.)
[tt]
Public Function ExecuteWebRequest(url As String) As String

Dim oXHTTP As Object

[blue]if instr(1,url,"?",1)<>0 then
url=url & "&cb=" & timer()*100
else
url=url & "?cb=" & timer()*100
end if[/blue]

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "GET", url, False
oXHTTP.send
ExecuteWebRequest = oXHTTP.responseText
Set oXHTTP = Nothing
'Debug.Print (ExecuteWebRequest)
End Function
[/tt]

 

[osx99]

Excellent, that works.
Thank you
Os