Help with web query

[bolpx001]

Hi All
Hope somebody can point me in right direction I have a large excel file of horses and the dates, courses and times of races but not the actual result. Ideally I would like to construct a web query to gather the information but I have no idea about how to go about it. If anybody could show me how to construct a web query for just one horse I could then develop it to cover all of them. Any help would be appreciated, even if it to say I am barking up the wrong tree and that it can't be done.
Many thanks Paul

[alrodopial]

Something like this?

Code: Select all

Sub RESULTS()

    Sheets("RPS").Select
 
  With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.racingpost.com/horses/result_runners_index.sd?r_date=2009-02-15" _
        , Destination:=Range("A1"))
        .Name = "result_runners_index.sd?r_date=2009-01-20"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

[bolpx001]

Many many thanks, this is even better (and faster) than what I was looking for. I cut and pasted the code and it ran perfectly.
But can I ask why are there two dates in the code

r_date=2009-02-15" _
.Name = "result_runners_index.sd?r_date=2009-01-20"

What is the signifance of the 2009-01-20 date

Many thanks again - Paul

[Ian]

I don't think it has any significance - you could probably delete the line and it will still work.

[KevinTHFC]

Many many thanks, this is even better (and faster) than what I was looking for. I cut and pasted the code and it ran perfectly.
But can I ask why are there two dates in the code

r_date=2009-02-15" _
.Name = "result_runners_index.sd?r_date=2009-01-20"

What is the signifance of the 2009-01-20 date

Many thanks again - Paul

The string after .Name is just an auto generated name for the query.
The r_date in the first line indicates the date of the results, therefore just chnage that to get anothers days results

[bolpx001]

Excellent, thank you all - Paul

[bolpx001]

I made some slight adjustments to the code above, to enable it to download multiple dates. The new code presupposes a sheet call dates exists and the dates required are list in column A and a blank sheet called RPS

Code: Select all

Sub RESULTS()

numDates = Application.InputBox("Enter Number of Dates to be processed")
ORGnumDates = numDates
getDate = Range(Sheets("dates").Cells(1, 1), Sheets("dates").Cells(1, 1)).Resize(numDates, 1)

' set starting position
Sheets("RPS").Select
r1 = 11
C1 = 1

Do While numDates > 0
    Range(Sheets("RPS").Cells(r1, 1), Sheets("RPS").Cells(r1, 1)) = getDate(numDates, 1)
    Connect = "URL;http://www.racingpost.com/horses/result_runners_index.sd?r_date=" & getDate(numDates, 1)
    r1 = r1 + 1
  With ActiveSheet.QueryTables.Add(Connection:=Connect, Destination:=Range(Cells(r1, C1), Cells(r1, C1)))
        .Name = "result_runners_index.sd?r_date=2009-01-20"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ' move to next recordd
    numDates = numDates - 1
    ' find next position for data
    r1 = Cells.SpecialCells(xlCellTypeLastCell).Row + 2
    Mess1 = ORGnumDates - numDates & " of " & ORGnumDates & " Processed"
   
    ' Prevent overflow
    If r1 > 60000 Then
        Mess1 = ORGnumDates - numDates & " of " & ORGnumDates & " Processed"
        numDates = 0
    End If
Loop

x = MsgBox(Mess1)

End Sub


[alrodopial]

Helpful addition Paul,
we thank you for sharing.

[bolpx001]

Its easy to build on excellent foundations
best regards - Paul