RP Link extractions from Web to EXCEL

[thunderfoot]

[Originally posted on MrEXCEL but 'adviced' they get a bit touchy about Betting related questions] Go to RacingPost (http://www.racingpost.co.uk/news/home.sd). Select a previous days Horse Racing results. Select a UK event. The race results for the selected day & course are shown in red. If you right click on each race (there will be 6 or 7 races for each course) and select Properties, a link will be shown similar to "result.sd?race_id=457578&r_date=2008-5-30',800,485,'no','yes','yes')". What I want to do is extract these links for each race at all UK race courses for that day, so that they would be listed one underneath each other in cells A1, A2, A3 etc. etc. so I would have, for example:- result.sd?race_id=457578&r_date=2008-5-30',800,485,'no','yes','yes') result.sd?race_id=457579&r_date=2008-5-30',800,485,'no','yes','yes') result.sd?race_id=457580&r_date=2008-5-30',800,485,'no','yes','yes') Is this possible to do in EXCEL using a Module!!!? Macro, or anything else?? ....... and, of course, if yes, how? Alternatively, rather than going to the 'Results' in RP, go to the 'Cards' and do a similar exercise. Ultimately, I want the UK race_id numbers for each day.

[sumuwin]

Re: RP Link extractions from Web to EXCEL This will do what you want. You can do up to 7 days at once. You just need to enter the start and end dates.

Sub RaceDates() Dim startdate, enddate Dim mystart, myend Dim startday, startmonth, startyear Dim endday, endmonth, endyear Dim myURL startdate = InputBox("Enter Start Date as dd/mm/yy") enddate = InputBox("Enter End Date as dd/mm/yy") Application.ScreenUpdating = False Range("A1").Select startday = Format(CInt(Day(startdate)), "00") startmonth = Format(CInt(Month(startdate)), "00") startyear = CInt(Year(startdate)) endday = Format(CInt(Day(enddate)), "00") endmonth = Format(CInt(Month(enddate)), "00") endyear = CInt(Year(enddate)) myURL = "URL;http://www.racingpost.co.uk/horses/result_search.sd?crs=&r_date=&start_search=1&search_title=Title+or+part+of&search_country=GB&search_course=Any" mystart = "&from_day=" & startday & "&from_month=" & startmonth & "&from_year=" & startyear & "" myend = "&until_day=" & endday & "&until_month=" & endmonth & "&until_year=" & endyear With ActiveSheet.QueryTables.Add(Connection:=myURL & mystart & myend, Destination:=ActiveCell) .FieldNames = True .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingAll .WebTables = "8" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = True .WebDisableDateRecognition = True .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With Cells.Select With Selection .VerticalAlignment = xlBottom .WrapText = False .MergeCells = False End With Columns("A:B").Select Selection.Delete Shift:=xlToLeft Columns("B").Select Selection.Delete Shift:=xlToLeft Range("A1").Select myrows = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To myrows If Cells(i, 1).Hyperlinks.Count > 0 Then Cells(i, 1).Offset(0, 1).Value = Cells(i, 1).Hyperlinks(1).Address End If Next i Columns("B:B").Select Selection.Replace What:="*horses/", replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Application.screenupdating=True End Sub

I have code that does the rest too (ie. get the results). all the best sumuwin

[thunderfoot]

Re: RP Link extractions from Web to EXCEL Thanks my man .... that is excellent. :nana I may well be contacting you again in the future, for some RP coding. ;) Have you any code to do the same but on future races?? Had a look at the code ...... if you use the '%' (wildcard) 3 times in the 'title' box, hence in your code (somewhere!!!), will that extend the search period to more than the seven days.

[sumuwin]

Re: RP Link extractions from Web to EXCEL No problem! Will take a look at future cards - I think I can get this from the runners index. Unfortunately I'm not able to get the future results!

[thunderfoot]

Re: RP Link extractions from Web to EXCEL

Had a look at the code ...... if you use the '%' (wildcard) 3 times in the 'title' box' date=' hence in your code (somewhere!!!), will that extend the search period to more than the seven days.[/quote'] Yeah Hey!!! :cow:nana Changing this part of the code ...... 'myURL = "URL;http://www.racingpost.co.uk/horses/result_search.sd?crs=&r_date=&start_search=1&search_title=Title+or+part+of&search_country=GB&search_course=Any" ... to read "search_title=%25%25%25" does the trick!!!

[sumuwin]

Re: RP Link extractions from Web to EXCEL Excellent spot! I think I'll need to rewrite in MS Access now - should be much quicker. cheers sumuwin

[Ajax]

Re: RP Link extractions from Web to EXCEL Hi Sumuwin, I have been trying something similar to the above but with no success. I am trying something similar to the original threadstarter in that I am attempting to extract results data from the RP website in Excel. I have tried the above code but all I am able to retrieve is javascript:OWSize(' Where have I gone wrong? Many thanks, Ajax.

[simonlulu8]

Re: RP Link extractions from Web to EXCEL Test Hi Sumuwin. Can you please take us up to date with how you would this for daily cards and results on the new RP site. Thanks and regards

[Trilobite]

Re: RP Link extractions from Web to EXCEL

Test Hi Sumuwin. Can you please take us up to date with how you would this for daily cards and results on the new RP site. Thanks and regards

Latest discussion on this topic is here ---> http://www.punterslounge.com/forum/f23/getting-data-rp-online-cards-29624/