Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
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, 1114 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,456
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,456
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,456
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,456
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, 1089 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,456
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,456
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, 1010 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


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 08:03 PM.


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