Getting Data from Multiple Webpages with Excel

[trevlyn1983]

I recorded a macro to get a table from a website . I need to know how to get the same table for more then 1 website . I know the range of the sites .

so for Instance .

http://www.wagertracker.com/Odds.aspx?date=3/31/2011&sport=MLB

thru

http://www.wagertracker.com/Odds.aspx?date=4/16/2011&sport=MLB

 
Sub Macro2()
'
' Macro2 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.wagertracker.com/Odds.aspx?date=3/31/2011&sport=MLB", _
        Destination:=Range("$A$1"))
        .Name = "2011&sport=MLB"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Rows("2:3").Select
    Range("A3").Activate
    Selection.Delete Shift:=xlUp
    Rows("3:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("A2").Select
    ActiveCell.FormulaR1C1 = " 3/31/2011"
    Columns("A:J").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A3").Select
    Columns("A:A").ColumnWidth = 39.71
    Columns("F:F").ColumnWidth = 35
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.ColumnWidth = 1.86
    Columns("A:A").ColumnWidth = 35
    Columns("E:E").EntireColumn.AutoFit
    Columns("K:K").EntireColumn.AutoFit
    Range("7:7,11:11,15:15").Select
    Range("A15").Activate
    Selection.Delete Shift:=xlUp
    Range("4:4,7:7,10:10,A2").Select
    Range("A2").Activate
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    Range("A14").Select
End Sub

any help would be appreciated . thanks

[matekus]

trevlyn,

Can you clarify the requirement - for example, do you want to enter a range of dates and have excel automatically create the worksheets and dynamically update them with data from the date-specific sites?

matekus

[trevlyn1983]

yeah .. I want the table 3 from each date specific page .
Like if you go to that link its the table with the scores and line . I want that table for the range of dates .

Is it possible to put all the data into 1 sheet or will there be multiple sheets ? either way is fine .

Im still a excel Noob for the most part heh

[uva3021]

grab the first page (3/31), then

Dim str_date as String
Dim URL as String

For i = 0 to 15

str_Date = "4/" & i+1
URL = "URL;http://www.wagertracker.com/Odds.aspx?date=" & str_Date & "/2011&sport=MLB"

code....

Next i
End sub

Replace your URL input below the table query with the URL string

[Pokerjoe]

http://forum.sbrforum.com/handicapper-think-tank/1021609-excel-data-importation-question.html

UVA, could you look at this? Thanks.

[uva3021]

http://forum.sbrforum.com/handicappe...-question.html

UVA, could you look at this? Thanks.

instead of betexplorer would it be preferable just to extract the lines directly from PInnacle?

[Pokerjoe]

instead of betexplorer would it be preferable just to extract the lines directly from PInnacle?

For historical odds? Betexplorer has odds for a gazillion leagues for many years past.
Also, I wanted to compare Pinny's efficiency to the general market.

[matekus]

Web Query Example (No Support)

trevlyn,

I have uploaded an example based on the requirements you outlined:
Note:

• Only change yellow entry fields;
• No support is provided; and
• Always comply with destination web site regulations.

matekus