OZmium Sports Betting and Horse Racing Forums - View Single Post

THE BASICS

The best way to learn web queries is to just jump right in and try them out.

First thing is to start the macro recorder, this is the best way to go when you are first learning macros as it will record anything you do in excel and record the format needed for visual basic the language the macro is writen in.

Create a new excel file then open it up, look at the very botton left next to where it says "Ready" you will see a button this starts the macro recorder.

Put in a name for the macro and then hit ok, go to the tab labeled Data at the top of excel then find "From Web".

In the address bar paste this address in http://tatts.com/racing/2012/2/1/MR/7 then press "Options" on the right, take note of the options on this page, we don't need to change anything here now so just click "Ok" then press "Go" on the next page.

After the page loads you will see yellow arrows these indicate the different tables on the page, for the first test we will use the whole page, this is the arrow in the upper left corner, select it and it will change to green,once selected press import you then need to select the location you want the import to start at, in this case =$A$1 should be shown, then press the properties button on the next page go to the botton and check mark "Overwrite existing cells with new data, clear unused cells" then press "Ok" then press "ok" again.

Once the page has been imported then press the record macro button again this will stop recording.

Right click Sheet1 and select "View Code" then double click "Modules" then double click "Module1" and your macro will be shown on the screen, start by clearing the green text as it is not needed as this is just comment text.

You should now see this code.

Code:


Sub Test()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))
        .Name = "7"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
   
    
    End With
End Sub

Now lets look at what we have, the first part is telling us where it is connected and the destination of where to place the info
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))

The rest of the information is what you would find on the options tab i showed you earlier and the properties tab, if you create another web query on another sheet and compare what is on those pages you can see where they are on here.

Most of this information i don't change.

Some pages you can get a specific table others you can't so it is trial and error some times.

Go to sheet2 and Follow the steps from starting the macro recorder and put this url in http://www.tab.com.au/Racing/Inform...w&RaceNumber=01

When you get to the webpage i want you to scroll down until you see the arrow just above the race prices, when you highlight it a box will go around the prices, this is what we want so click it.

Then continue on with the rest of the steps until you stop the macro recorder.

Now see we just have the prices, now go in to view code and look at module2 you should have this.

Code:


Sub Macro2()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.tab.com.au/Racing/Information/Results/DisplayDetailedView.aspx?State=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01" _
        , Destination:=Range("$A$1"))
        .Name = "00&DisplayType=FinalDividendView&RaceNumber=01"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """BetGrid_DGTableOne2"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Scroll down to .WebSelectionType = xlSpecifiedTables see how this is different from the first one.
If you look you will also see a new line has been added .WebTables = """BetGrid_DGTableOne2""" this is the name of the table we selected, different websites use different names for this, some sites use numbers.

If you want to try different races for these websites you need to changed these parts in red.

http://tatts.com/racing/2012/2/1/MR/7

http://www.tab.com.au/Racing/Inform...=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01

The NSW/VIC tab urls change before and after the race so you can just use the url that is shown on the page you want to import.

To rerun the macros again for a new page just go to "view" tab at the top of excel then "Macros" and double click the macro you want to run.

That's it for now, try this on a few different sites you are trying to get data from and see what you can import.