OZmium Sports Betting and Horse Racing Forums - View Single Post

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

Code:


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

Code:


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

Code:


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.Connect  ions.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.