I am trying to update my webquery to MSXML2.XMLHTTP using the code in this posting

I have tried to modify it as below:-

Dim WA As String

Sub queryhtml(wks As Worksheet)

WA = wks.Range("AF1").Value
Formhtml = ExecuteWebRequest(WA)

It will not run the Formhtml function; just skips it and exits the sub.

Any help would be greatly appreciated as I am completely out of my depth here.

Ta,
Martin

If you post the whole code for that macro i might be able to see where the issue is.

Post it with code tags thanks.

Hi


Sub queryhtml(wks As Worksheet)
Dim WA As String
WA = wks.Range("AF1").Value



Formhtml = ExecuteWebRequest(WA) ' runs the first function.

outputtext (Formhtml) 'gets the contents held in the file created by the second function

Set Temp_qt = wks.QueryTables.Add(Connection:=WA, Destination:=wks.Range("BA1")) ' creates the link from the tx doc to the sheet.

With Temp_qt
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

' wks.QueryTables.Item(1).Delete

' Set Temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt" 'deletes the temp txt file.
' If ThisWorkbook.Connections.Count > 0 _
' Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect ions.Count).Delete 'deletes any left over connections.

End Sub

Whoops forgot the wordy bits

wks. is because I run the same code on multiple sheets - 1 for each race up to 70 a day. So instead of writing Range("AF1").Value I write wks.Range("AF1").Value and that tells the computer to work on the current sheet. I use OnTime to do the timing. Each minute it checks each sheet to see if it needs to be updated and if so runs the code.

The first step is download the data and as Tatts xml is not updating properly I am using web query. But the old TABonline page will not stay up forever so want to modify yr code so I can get it off the Tatts page. And then maybe NSW

Ta,
Martin

I am not the best at reading code to find issues but have you tried running in debug mode?

Open your code page and position the cursor at the start then select debug then every time you press F8 it will run one line of code and you will see where at goes and you can even look at your sheet to see the outcome.

If it is crashing on the formhtml at usually means the web address returned an erro so just for a test copy the actual web address from the web page you after and past it between the bracket and between "" these.

If when you run it again in debug it does what it should then the issue is in your web address.

As far as using Taboline site you should have no issues except i find it slower than the old site, takes about 5 to 10 seconds to import as the old site takes about 3 seconds or less.

I have no idea with the new NSW/Vic sites as i can't convert the new code to what i know.

Just had another try and it gets to oXHTTP.send in the Public Function ExecuteWebRequest and then exits - does not go onto ExecuteWebRequest = oXHTTP.responseText

Got it working but found tatts to be very slow. Tried there xml feed again and still getting 30% error rate. So will stick with what I have for now and try to get into NSW's xml. This exercise has given me a few hints about that

I was of the belief that the XML feeds from NSW & Vic sites will no longer be available once the old sites are shut down, which as it happens is today.

Old sites are gone & none of my Vic code is working so I guess that's that...

Yes, unless you use there new service, but importunately even they don't know how to use it.

Re Shawns's post
Well that's encouraging!
I tried reading the Live Odds book again last night. Rather vague to a person of my limited skill. But then then whole xml thing seems to be like that. Reading all the various pages there is lots of data but even the pro's seem to struggle to make it work.
I am hoping that the Tatt's page, http://www.tabonline.com.au, stays up until they get their xml back on track. I really miss their xml feed it was so sweet and simple even I could use it. Still I have recorded every race today so far of tabonline and will get the balance and it is running fine.

Ta,
Martin