NSW TAB Live Odds help needed

[womp]

Hi everyone,

I am having trouble trying to get info from NSW TAB live odds. By trouble I mean I wouldn't have a clue where to start. I have been trying to understand how it all works, but it is going way over my head at the moment. I can get the fields etc from Tatts (UniTab?) xml feed mainly thanks to their example they give you. Why can't the NSW TAB do that?! It would make it a little easier!

Any help people could give me, even if it's just getting the Access Key, would be greatly appreciated.

Cheers,
Chris.

[Shaun]

You need to use a web query to pull the data in, you will also need a way to manipulate the race number and venue.

I would set it up in a drop down list then add today's markets with the variables needed.

Here is a simple web query macro


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
End Function



Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
MyFile = ThisWorkbook.Path & "\temp.txt"
fnum = FreeFile()
Open MyFile For Output As fnum
Print #fnum, text
Close #fnum
End Function



Sub Race()
Application.ScreenUpdating = False
With ThisWorkbook
.Sheets("Prices").Select
Range("BJ1:BU100").Select
Selection.ClearContents
formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Control").Range("AL4").Value)
outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("Prices").QueryTables.Add(Connection:= _
"URL;" & ThisWorkbook.Path & "\temp.txt" _
, Destination:=ThisWorkbook.Sheets("Prices").Range("$BJ$1"))
With temp_qt
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveSheet.QueryTables.Item(1).Delete
Set temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt"
If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete
.Sheets("Control").Activate
Application.ScreenUpdating = True
End With
End Sub

This is the formula i have in cell AL4
=IF(AK2="","","http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1"&"&MeetingCode="&AN2&"&RacingCode="&AO2&"&FromDate="&TEXT(AS1,"yyyy-mm-dd")&"T00:00:00&BetType=Win&RaceNumber="&AL3)

The parts in red are the variables like what state meeting codes date and race number.

There is a lot more involved but this would work if you stuck it in a sheet and added the variables at the correct locations with the same sheet names.

You can run multiple macros pulling in different info, you only need the first 2 macros once you don't need them for each query.

[Neurokahuna]

Hi Shaun,

Just wondering if this VBA code you have uploaded is doing a simple screen scrape of a race, or is actually using the Live Odds WSDL and methods?

Only reason I ask is that I didn't even know you could call SOAP Web services through VBA (if in fact, you are using the SOAP services).

Neurokahuna

[womp]

Thanks Shaun for your reply, I will give it a go and have a play around with a few things to get the info I need.

It would be a lot easier to do it your way than through the Live Odds SOAP web service. Although if anyone knows how to go about it I would still like to know just out of interest.

Cheers,
Chris.

[Shaun]

My way is simple screen scrap, downloads to a temp txt file then pastes it in excel.

It has only been in the last 12 months i have changed the way i get web info using the CreateObject("MSXML2.XMLHTTP") method, the speed difference is huge.

I have used the xml feeds before but unless you know what to look for can be a bit harder to get the info and not all sites use xml.

I started a thread here http://forums.ozmium.com.au/showthread.php?t=23333 to try and help people understand the web query.