OZmium Sports Betting and Horse Racing Forums

#41

20th February 2012, 03:40 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Been a week since i posted here and just thought i would add a couple files to show the speed difference the extra code i added makes from the standard web query.

There are 2 files here that are the same on the outside but the macros on the After sheet have been updated the the method i use now for imports.

The sheets are just designed to down load unitab data and select the runner in barrier 1 with results if available, give then a test to see the difference.

Press "Start" put in date then you just need to select a venue and put in the first race number and the last race number then hit run.

Attached Files

	UniTab Inside Barrier System Bofore.xlsm (701.4 KB, 579 views)
	UniTab Inside Barrier System After.xlsm (682.5 KB, 595 views)

#42

20th February 2012, 03:47 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Sorry on last post you don't need to add the last race it will know already just need to ad first race.

#43

22nd February 2012, 11:16 PM

Puntz

Member

Join Date: Jan 2012

Posts: 291

Shaun, I'll post the same Race List Workbook, that way it's part of these lessons
If most of the process can be formula to get the columns right, then good.

Some F4 races webs in on separate rows, so that had to be sorted to.

Attached Files

NEW-AUTO-DAILY RACE LIST-20120222.xlsm (76.3 KB, 485 views)

#44

27th February 2012, 10:20 PM

Puntz

Member

Join Date: Jan 2012

Posts: 291

Shaun,
Not sure where the error is for the URL reference formula in Sheet 3-Range G1, tried to type in the formula with direct references from the cells.
The web query is in Module1.

The purpose is, so when a race is finished or done with looking at, the referenced race list row can be deleted and on with the next race on the list.

Got it to work OK with tats.com, but this one seems a bit stubborn
if can you check it out, much appreciated.

Thanks,

Attached Files

xml TEST_20120227 ws.xlsm (55.2 KB, 471 views)

#45

10th October 2012, 06:08 PM

darkshines darkshines is offline

Banned

Join Date: Oct 2012

Posts: 3

@Shaun

Excellent excellent work with this. Been a brilliant help. I'm essentially just importing tote odds direct into my punting worksheet, which allows easy comparison, kelly bet calcs etc etc - no more manual data entry.

I've basically got to BASICS PART 2 of your 'course', just needed some help with PART 3. Working fine just want to make it quicker.

Am importing from ozbet (unfortunately name/odds table doest work for some reason so need to grab whole screen), got meeting ID, date in cells A1 and A2 of sheet 'ID'. Have created a list of individual macros for each race of the day, then another macro to run them all depending on how many races in the meeting, so I update all tote prices for meeting at once.

I've shown below the macro I've got for importing Race 1. What I want to do is incorporate the Public Functions (from your 5th Feb post) to speed things up, cos it does take a while.

Are you able to perhaps point out what I need to do to this code to enable the Public Functions to work. My VBA skills aren't quite at that level yet to follow your example.

Code:


 
Sub Race_1_tote()
' Race_1_tote() 
Macro
	With Sheets("tote").QueryTables.Add(Connection:= _
		"URL;https://www.ozbet.com.au/UISecure/ToteUI/RaceTote.aspx?fixtureId=" & _
		Sheets("ID").Range("a1").Value & _
		"&fixtureDate=" & _
		Sheets("ID").Range("a2").Value & _
		"&contestNumber=" & _
		"1", 
Destination:=Range("tote!$A$10"))		
.Name = "RaceTote.aspx?fixtureId=" & _
		Sheets("ID").Range("a1").Value & _
		"&fixtureDate=" & _
		Sheets("ID").Range("a2").Value & _
		"&contestNumber=" & _
		"1"
		.FieldNames = True
		.RowNumbers = False
		.FillAdjacentFormulas = False
		.PreserveFormatting = True
		.RefreshOnFileOpen = False
		.BackgroundQuery = True
		.RefreshStyle = xlInsertDeleteCells
		.SavePassword = False
		.SaveData = True
		.AdjustColumnWidth = False
		.RefreshPeriod = 0
		.WebSelectionType = xlEntirePage
		.WebFormatting = xlWebFormattingNone
		.WebPreFormattedTextToColumns = True
		.WebConsecutiveDelimitersAsOne = True
		.WebSingleBlockTextImport = False
		.WebDisableDateRecognition = False
		.WebDisableRedirections = False
		.Refresh BackgroundQuery:=False
	End With
End Sub

Thanks in advance!!

#46

10th October 2012, 10:28 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Try this out, if you can't get it working upload the sheet so i can take a look.

I also included the 2 functions so remove them if you have them already.

Code:


Public Function ExecuteWebRequest(url As String) As String
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing
End Function

Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
        MyFile = ThisWorkbook.Path & "\temp.txt"
        fnum = FreeFile()
        Open MyFile For Output As fnum
        Print #fnum, text
        Close #fnum
End Function


Sub Race_1_tote()
' Race_1_tote()
    Sheets("tote").Select
    Range("A10:Z500").Select
    Selection.ClearContents
    formhtml = ExecuteWebRequest("URL;https://www.ozbet.com.au/UISecure/ToteUI/RaceTote.aspx?fixtureId=" & _
    Sheets("ID").Range("a1").Value & "&fixtureDate=" & Sheets("ID").Range("a2").Value & "&contestNumber=" & "1")
    outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("tote").QueryTables.Add(Connection:= _
    "URL;" & ThisWorkbook.Path & "\temp.txt" _
    , Destination:=ThisWorkbook.Sheets("tote").Range("$A$10"))
    With temp_qt
        .Name = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
ActiveSheet.QueryTables.Item(1).Delete
    Set temp_qt = Nothing
        Kill ThisWorkbook.Path & "\temp.txt"
    If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect  ions.Count).Delete
End Sub

__________________
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 : 10th October 2012 at 10:30 PM.

#47

11th October 2012, 12:04 PM

darkshines darkshines is offline

Banned

Join Date: Oct 2012

Posts: 3

Hi Shaun

Thanks for the very prompt reply. Couldn't get it to work, so have posted it for you to tinker with as suggested.

As you will see I import all races onto one tab, then I can apply vlookup to bring tote odds into my form worksheet.

On the ID tab, cell A3 is obviously the number of races for that given meeting.

Thanks again, you're a champ.

Attached Files

tote import macros rev1.xlsm (98.6 KB, 420 views)

#48

11th October 2012, 06:26 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Run the test_tote macro, it is located on the second module, if you need multiple macros that is fine as long as they are on same module you only need is one set of functions.

Attached Files

tote import macros rev1update.xlsm (44.0 KB, 473 views)

#49

11th October 2012, 07:01 PM

darkshines darkshines is offline

Banned

Join Date: Oct 2012

Posts: 3

Beauty Shaun!

Just gave it a go, works like a charm. Thanks a bunch. Speed is phenomenal.

Only really stumbled on this forum lately, will try and give a bit back in the future.

Big thumbs up from me.

#50

11th October 2012, 07:04 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Np, that's why i made this post so people could take what i know and use it themselves and learn, if you get stuck on anything let me know,chances are i may have used it before.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

	To advertise on these forums, e-mail us.