OZmium Sports Betting and Horse Racing Forums

24th July 2011, 01:46 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Excel Help

I need some help with web query, i am importing pages from Cyberhorse but having issues with formatting, here is a page from today.

http://formguide.cyberhorse.com.au/...=Coffs&raceno=5

When i import the page the formats for some of the Distance,track and going get all messed up, seams if the number of starts is over 10 it changes the format, i have tried a few things with no success so am after some help from more experienced with excel.

24th July 2011, 06:28 PM

4legs

Member

Join Date: Mar 2009

Posts: 96

Shaun, if you email me your web query I can have have a quick look at it and see if I can spot any problems .. Fred

24th July 2011, 07:30 PM

The Elk

Member

Join Date: Sep 2008

Posts: 28

Shaun,
Is this the info you are trying to extract? (stats from 1st runner Blinkin Easy)

Quote:

Distance: 13:2-1-3
Track: 3:0-0-0
Good: 31:5-5-3
Dead: 9:3-0-2
...etc

I'm not quite sure what you mean by "it gets all messed up over 10 starts"?

My excel web queries are a bit rusty but vb2010 is pretty close:

1. extract Dis = "13:2-1-3" using Instr()
2. Career()=Split(Dis,":") giving Career(0)="13" & Career(1)="2-1-3"
3. Place()=split(Career(1),"-") so Place(0)="2" Place(1)="1" Place(2)="3"

Is this what you are trying to do and having troubles when runs at distance > 9 , Good=31...etc?

Probably need a little more info to give any further help

cheers

24th July 2011, 08:00 PM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

The extraction is not the problem the problem is it wants to sometimes format the number as a date, even if i have the sheet formatted as text when i import the new sheet it just goes back.

24th July 2011, 11:14 PM

The Elk

Member

Join Date: Sep 2008

Posts: 28

Aaaaaahhhh, now I understand - can you change the dashes to dots/periods or anything other than dashes so that excel does not recognise it as a date

cheers

5th August 2011, 12:36 AM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

I have found a solution to this problem thanks to a user on the Gruss forum, he uses some macros to change 10:1-2-0 to 10::1-2-0 the double :: confuses excel and stops it changing to a date format.

I will post the macro but i don't totally understand it but it works, there are 3 parts to the macro.

The part in red in macro 3 is what he has added to my macro.

Forgot to mention what ever he has done has changed the import time from 30 seconds a race to 5 seconds.

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

Code:


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

Code:


Sub form()
Sheets("Form").Select
    Sheets("Form").Range("B1:J1000").Select
    Selection.ClearContents
    Columns("B:J").Select
    Selection.NumberFormat = "@"
    formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Data").Range("A1").Value)
    formhtml = Replace(formhtml, ":", "::")
    outputtext (formhtml)
    
    Set temp_qt = ThisWorkbook.Sheets("Form").QueryTables.Add(Connection:= _
            "URL;" & ThisWorkbook.Path & "\temp.txt" _
            , Destination:=ThisWorkbook.Sheets("Form").Range("B1"))
    With temp_qt
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Set temp_qt = Nothing
    Kill ThisWorkbook.Path & "\temp.txt"
    If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect  ions.Count).Delete

Columns("B:J").Select
    Selection.Copy
    Application.DisplayAlerts = False
    Sheets("Data").Select
    Range("AA1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("AA:AA").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), _
        TrailingMinusNumbers:=True
        Columns("AC:AC").Select
    Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
        Application.DisplayAlerts = True
        Range("A1").Select
        
End Sub

Last edited by Shaun : 5th August 2011 at 12:41 AM.

5th August 2011, 12:47 AM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Looking at the code it seams he loads the page in to a temp file first then makes the changes needed then imports to excel, this would results in the speed increase as it does not have to wait for excel to import the page, i have used a similar idea in another program before.

5th August 2011, 08:22 AM

moeee

Suspended

Join Date: Jan 1970

Location: Melbourne

Posts: 5,359

Quote:

Originally Posted by Shaun

I have found a solution to this problem thanks to a user on the Gruss forum, he uses some macros to change 10:1-2-0 to 10::1-2-0 the double :: confuses excel and stops it changing to a date format.

And perhaps a little bit of acknowledgement to Member "The Elk" , who suggested you do just that , wouldn't go astray

5th August 2011, 09:53 AM

Shaun

Member

Join Date: Jan 1970

Location: Western Australia

Posts: 3,456

Although at times i may not acknowledge help i get from others it is always appreciated, this is one of the main reasons i provide as much help as i can with excel sheets and other things.

I visit many forums on my travels and in the past this place has copped some flak over editing of urls and restriction on things we can discuss, but things have changed around here for the better and some may disagree with some of the topics that are posted and other may just brush it off as rubbish but this is still the most informative and useful forum around.

#10

5th August 2011, 10:32 AM

The Elk

Member

Join Date: Sep 2008

Posts: 28

Thanks Moeee for that (certainly wasn't expected - but appreciated) and thanks also to Shaun for posting a workable solution to the problem

cheers

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.