Excel - How to tell if bet successful ?

[Captain Sensible]

http://www.racingpost.co.uk/horses/desktop_results.sd

is usually quick and should be easy to parse the winners out

[dgs2001]

Thanks

I'm hoping to leave my pc unattended and need results for increasing stakes and loss recovery.

I was just getting all confident today when a dead heat threw a spanner in the works and £160 down the pan !!

Ah Well

The programs great just the human element gets in the way sometimes

[tkp]

thanks everyone.

I was thinking about the racing post's fast results - not sure how the need to log in will affect the web query....I'll give it a go though.


So no-one already doing this ?

[GeorgeUK]

I have no life.

Code: Select all

Public ie As InternetExplorer

Sub RacingPost_Login()
 
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
'Go to this Web Page!
ie.navigate "https://reg.racingpost.co.uk/cde/login_iframe.sd"
'Check for good connection to web page loop!
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until ie.Busy = False
DoEvents
Loop

With ie.document.forms(0)
    .in_un.Value = "USERNAME"
    .in_pw.Value = "PASSWORD"
    .submit
End With

'Check for good connection to web page or you won't login
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until ie.Busy = False
DoEvents
Loop
End Sub

Sub RacingPost_Results_Import()

Call RacingPost_Login

'Check first page loads ok or it will hang.
Do Until ie.readyState = READYSTATE_COMPLETE
    DoEvents
Loop
Do Until ie.Busy = False
    DoEvents
Loop

1
r = 0
c = 0
'Go to this Web Page!
ie.navigate "http://www.racingpost.co.uk/horses/desktop_results.sd"

'Check for good connection to web page loop!
Do Until ie.readyState = READYSTATE_COMPLETE
    DoEvents
Loop
Do Until ie.Busy = False
    DoEvents
Loop

' type STOP in cell A1 to stop the macro/refresh

If Range("A1").Value = "STOP" Then ie.Quit
If Range("A1").Value = "STOP" Then Exit Sub

'****************************************
'NOTE: this part will clear the entire worksheet that is currently open.
'Amend so that only the result details are being deleted before the new data comes in.
'I am just testing so am pasting into Sheet2.  Just don't delete anything important.  Please!
'****************************************


Cells.Select
Selection.Clear '.Delete
Range("A1").Select
Dim oResultPage As HTMLDocument
Dim AllTables As IHTMLElementCollection
Dim xTable As HTMLTable
Dim TblRow As HTMLTableRow
Dim myWkbk As Worksheet
 
'copy "data" table
Set oResultPage = ie.document
Set AllTables = oResultPage.getElementsByTagName("table")
'Set xTable = AllTables.Item(10)
Set myWkbk = ActiveWorkbook.Sheets("Sheet2")
For Each xTable In AllTables
For Each TblRow In xTable.Rows
    r = r + 1
    For Each tblCell In TblRow.Cells
        c = c + 1
        myWkbk.Cells(r, c) = tblCell.innerText
    Next tblCell
    c = 0
Next TblRow
Next xTable
' refresh values every 5 secs
s = Now
Do Until Now >= s + TimeValue("00:00:05")
DoEvents
Loop
GoTo 1
End Sub


Never tried using logins before so it took a little longer than i would have liked.

Be VERY careful using this code. I have it set up to delete the activesheet when refreshing the results whilst putting the results on Sheet2. Don't say i didn't warn you. Anyone using this code wil need to specify either sheets or columns that they want the results on and make sure the cells.select part is changed to only that range or it will keep deleting whatever is on the sheet you are looking at... every 5 seconds.

Hope it's of use. Just run the "RacingPost_Results_Import" macro and it should all work.
The only time an error came up for me was when i was already logged in so i couldn't enter my username as the textboces didn't appear. Make sure you are logged out before running this and it should be ok. I think

[dgs2001]

George I Think this could be a U-Beauty Moment !!


You would'nt happen to be able to guess why my spreadsheets keep freezing and shutting down excel would you??

It seems like my formulas are always fighting my vba code. I even get the debugger appearing on lines like

Application.Calculation = XlManual

and my subs seem to stop on if statements when the ifs arent satisfied like the code ignores the Then .

Any ideas anyone ??

Thanks Duncan

[GeorgeUK]

Application.calculation should not be bringing up a debug error.

The only thing i can think of is that you are missing an end if, or end with or next and the code is trying to highlight where it thinks the end of the code is. Have you stepped through the code using F8? and try going to debug and compile in the VB Editor before running the code. You might need to start another thread for your problem as that's all i can think of. Feel free to post up your code though.

[tkp]

Nice one George !!

I'll give it a go tomorrow.

[GeorgeUK]

Forgot to mention something you will need to get the code to work

When you paste the code into the VB Editor, goto Tools - References...
Tick Microsoft Internet Controls
Tick Microsoft HTML Object Library

Now the code should run ok.
Sorry for any confusion caused.
And of course you need to change USERNAME & PASSWORD