PDA

View Full Version : Excel Help


Shaun
24th July 2011, 12:46 PM
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/index.php/Form/form.html?formdate=2011-07-24&track=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.

4legs
24th July 2011, 05:28 PM
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

The Elk
24th July 2011, 06:30 PM
Shaun,
Is this the info you are trying to extract? (stats from 1st runner Blinkin Easy)

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

Shaun
24th July 2011, 07:00 PM
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.

The Elk
24th July 2011, 10:14 PM
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

Shaun
4th August 2011, 11:36 PM
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.


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 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.Connections.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

Shaun
4th August 2011, 11:47 PM
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.

moeee
5th August 2011, 07:22 AM
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 :)

Shaun
5th August 2011, 08:53 AM
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.

The Elk
5th August 2011, 09:32 AM
Thanks Moeee for that (certainly wasn't expected - but appreciated) and thanks also to Shaun for posting a workable solution to the problem

cheers

moeee
5th August 2011, 10:38 AM
And in case anyone was offended by my post , Your Integrity ,Honesty and Generosity is well known by myself Shaun.
I just figured you either missed or forgot The Elk Input.

And now I'ld like to get the Program I use , to cut down from 30 seconds to 5 seconds.

Has anyone looked at Shauns Code and worked out why the Massive Gain in wasted time is acheived?

Or is 5 seconds the correct time , and Shaun had an error in his programming that was causing it to take so long at 30 seconds?

Shaun
5th August 2011, 03:15 PM
Oh i know why it has changed by the way the input is done, i can replicate this now i know what it is doing.

If you put your excel web query in the forum i can do it for you.

moeee
5th August 2011, 04:48 PM
See if this one can be speeded up.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://edog.grv.org.au/webwiz/wwiz.asp?wwizmstr=WWIZ.DOG.INFO2&STATE=V&SORT.FLAG=D&FROM.DD=01&FROM.MM=01&FROM.YYYY=1986&TO.DD=03&TO.MM=08&TO.YYYY=2011&PLACE=ALL&TRACK=ALL&DIST=ALL&DOG=bootmaker", _
Destination:=Range("A1"))
.Name = "GRV"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Shaun
5th August 2011, 06:23 PM
At the top of a Module put these 2 macros


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


You only need these once in the module then you can add as many different web queries as you want.

Then add this to your macro.


Sub test()
formhtml = ExecuteWebRequest("https://edog.grv.org.au/webwiz/wwiz.asp?" & _
"wwizmstr=WWIZ.DOG.INFO2&STATE=V&SORT.FLAG=D&FROM.DD=01&FROM.MM=01&FROM.YYYY=1986&TO.DD=" & _
"03&TO.MM=08&TO.YYYY=2011&PLACE=ALL&TRACK=ALL&DIST=ALL&DOG=bootmaker")
outputtext (formhtml)

Set temp_qt = ThisWorkbook.Sheets("Sheet1").QueryTables.Add(Connection:= _
"URL;" & ThisWorkbook.Path & "\temp.txt" _
, Destination:=ThisWorkbook.Sheets("Sheet1").Range("A1"))
With temp_qt
.Name = "GRV"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "5"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub



Just adjust to your needs.