Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
Reload this Page download unitab horse data into excel spreadsheet - problem
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
Page 3 of 8 < 1 2 3 4 5 6 7 8 >
 
Thread Tools Search this Thread Display Modes
  #21  
Old 12th November 2010, 09:46 AM
kennedy22384 kennedy22384 is offline
Member
  
Join Date: Oct 2005
Posts: 52
Default
Updated, still increasing file size, maybe to do with the fact it is xls. If you can save it as a xlsm file
Attached Files
File Type: xls Kwikpix Redux.xls (837.5 KB, 1291 views)
Reply With Quote
  #22  
Old 12th November 2010, 09:51 AM
kennedy22384 kennedy22384 is offline
Member
  
Join Date: Oct 2005
Posts: 52
Default
the issue with the racelist is to do with lines 100-101 in the code. Cause im on a mac, you may need to play with it and trial and error
Reply With Quote
  #23  
Old 12th November 2010, 10:31 AM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
Code:

Sub GetRaces2()
    Sheets("RaceList").Select
    Sheets("RaceList").Range("A2:AB500").Select
    Selection.ClearContents
    Sheets("Races2").Select
    Sheets("Races2").Cells.Select
    Selection.ClearContents
    With Sheets("Races2").QueryTables.Add(Connection:= _
        "URL;http://formguide.cyberhorse.com.au/index.php/Form/view-form.html" _
        , Destination:=Sheets("Races2").Range("$A$1"))
        .Name = False
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With
    A = 2
    B = 2
    strquote = Chr$(34)
    TrackName = vbNullString
    RaceNumber = 0
    Do While Sheets("Races2").Cells(A, 1) <> vbNullString Or Sheets("Races2").Cells(A + 1, 1) <> vbNullString
        If IsNumeric(Sheets("Races2").Cells(A, 1)) = False Then
            TrackName = Sheets("Races2").Cells(A, 1)
            If TrackName = "Port Macquarie" Then TrackName = "Pt Macquarie"
            A = A + 2
        End If
        If IsNumeric(Sheets("Races2").Cells(A, 1)) = True Then
            RaceNumber = Sheets("Races2").Cells(A, 1)
            Sheets("RaceList").Cells(B, 1) = TrackName
            Sheets("RaceList").Cells(B, 2) = RaceNumber
            Sheets("RaceList").Cells(B, 3) = "=GetAddress(Races2!E" & A & ")"
            Sheets("RaceList").Cells(B, 4) = "=SUBSTITUTE(C" & B & "," & strquote & "&" & strquote & "," & strquote & "&&" & strquote & ")"
            Sheets("RaceList").Cells(B, 4) = "http://" & "formguide.cyberhorse.com.au/index.php/Form/form.html?" & Right(Sheets("RaceList").Cells(B, 4), Len(Sheets("RaceList").Cells(B, 4)) - 80)
            Sheets("RaceList").Cells(B, 5) = "'" & Mid(Sheets("RaceList").Cells(B, 4), 70, 10)
            A = A + 1
            B = B + 1
        End If
    Loop
    RaceCount = B - 1
End Sub


My vba is limited but i can see where the problem is, in the above code if all we need is the address can't we just use what is outputted in column C
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Reply With Quote
  #24  
Old 12th November 2010, 10:35 AM
kennedy22384 kennedy22384 is offline
Member
  
Join Date: Oct 2005
Posts: 52
Default
No, for some reason the address that comes out is incorrect. i have to put in an extra 2 lines of code for column D. the first one adds an extra ampersand as excel removes the ampersand from any webpages. The second line of code sets up the correct webpage. the incorrect address does provide the information to convert it to the correct address.
Reply With Quote
  #25  
Old 12th November 2010, 12:44 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
I will have a play with it when i get time, if i get it working i will create a ratings sheet that anyone can add there own figures to each form factor to make there own ratings.
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Reply With Quote
  #26  
Old 15th November 2010, 10:06 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
I am having an issue, i am using part of your code but it keeps dropping race 1 from each venue when it creates the racelist, bit of help needed.

Code:


'Global RaceCodes(1 To 20) As String
'Global RaceNumbers(1 To 12) As Integer
Global RaceCount As Integer
Global RaceNo As Integer
Global RaceCode As String
Sub RunAll()
    Application.Calculation = xlCalculationAutomatic
    GetRaces2
        For RaceNo = 2 To 2 'RaceCount
        Next RaceNo
End Sub
Sub GetRaces2()
    Sheets("RaceList").Select
    Sheets("RaceList").Range("A2:AB500").Select
    Selection.ClearContents
    Sheets("Races2").Select
    Sheets("Races2").Cells.Select
    Selection.ClearContents
    With Sheets("Races2").QueryTables.Add(Connection:= _
        "URL;http://formguide.cyberhorse.com.au/index.php/Form/view-form.html" _
        , Destination:=Sheets("Races2").Range("$A$1"))
        .Name = False
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With
    A = 2
    B = 2
    strquote = Chr$(34)
    TrackName = vbNullString
    RaceNumber = 0
    Do While Sheets("Races2").Cells(A, 1) <> vbNullString Or Sheets("Races2").Cells(A + 1, 1) <> vbNullString
        If IsNumeric(Sheets("Races2").Cells(A, 1)) = False Then
            TrackName = Sheets("Races2").Cells(A, 1)
            If TrackName = "Port Macquarie" Then TrackName = "Pt Macquarie"
            A = A + 2
        End If
        If IsNumeric(Sheets("Races2").Cells(A, 1)) = True Then
            RaceNumber = Sheets("Races2").Cells(A, 1)
            Sheets("RaceList").Cells(B, 1) = TrackName
            Sheets("RaceList").Cells(B, 2) = RaceNumber
            Sheets("RaceList").Cells(B, 3) = "=GetAddress(Races2!E" & A & ")"
            Sheets("RaceList").Cells(B, 4) = "=SUBSTITUTE(C" & B & "," & strquote & "&" & strquote & "," & strquote & "&&" & strquote & ")"
            Sheets("RaceList").Cells(B, 4) = "http://" & "formguide.cyberhorse.com.au/index.php/Form/form.html?" & Right(Sheets("RaceList").Cells(B, 4), Len(Sheets("RaceList").Cells(B, 4)) - 80)
            Sheets("RaceList").Cells(B, 5) = "'" & Mid(Sheets("RaceList").Cells(B, 4), 70, 10)
            A = A + 1
            B = B + 1
        End If
    Loop
    RaceCount = B - 1
End Sub

Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace _
    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Reply With Quote
  #27  
Old 21st November 2010, 01:56 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
Using code provided by kennedy22384 i have put together a sheet that will download each race and output ratings, the value of each form figure can be changed on the "Tables" sheet just change the figures in red to suit your own ratings, if you wish to exclude a form criteria from your ratings just place an "X" in the box above that list to exclude.

You will need to remove all the days races that i have tested it on so delete all sheets after the "Data" sheet, there are still plenty of bugs and it crashes after a while and takes about 30 seconds a race to update so i still have work to do, or maybe others can solve the issues.

To start the sheet go to the "Data" sheet and hit "Start" If it does crash just hit Debug then close the VBA window and press "Re-Start" you may lose the info for that race but it should continue on.

I hope to have bugs fixed in a few days.
Attached Files
File Type: xls Form Ratings 2010.xls (1.61 MB, 1263 views)
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Reply With Quote
  #28  
Old 21st November 2010, 07:51 PM
kennedy22384 kennedy22384 is offline
Member
  
Join Date: Oct 2005
Posts: 52
Default
Cool Shaun, I have just been data mining for greyhound's names for the last 5 days or so, i will test when im done with that cause then i got to get the form for each dog and let me say, this will take a while.
Reply With Quote
  #29  
Old 21st November 2010, 08:23 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
I found a few bugs and am adding more ideas so that is just a rough draft
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Reply With Quote
  #30  
Old 21st November 2010, 11:47 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,457
Default
Fixed a few bugs and added the option to import single race one at a time.
I also added a delete button that will delete all the extra sheets that are created when the days racing is done.

Open the sheet go to data and press Start this imports the race lists, then you have the option to use Automatic to import all races or Manual for a single race.

For single race option just click on the first cell of the race you want on the left hand side then press Manual
Attached Files
File Type: xls Form Ratings 2010.xls (1.13 MB, 1186 views)
__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________
Last edited by Shaun : 21st November 2010 at 11:50 PM.
Reply With Quote
Reply
Page 3 of 8 < 1 2 3 4 5 6 7 8 >

« Previous Thread | Next Thread »

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 10:42 PM.

Contact Us - OZmium Pty Ltd - Archive - Terms of Use - Privacy Statement - Top

Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655