OZmium Sports Betting and Horse Racing Forums

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - Excel Help (http://forums.ozmium.com.au/showthread.php?t=22134)

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?

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.

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

At the top of a Module put these 2 macros

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

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.

Code:



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.