OZmium Sports Betting and Horse Racing Forums - View Single Post

THE BASICS PART 3

Now we have a couple of ways to get info in to the sheet we need to make it more efficient, this will also speed up the time it takes to import.

We need to add a couple new macros to the sheet, these macro should be added to the top of the sheet before the inport macros or any other macros you have added.

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

The first code uses the MSXML2.XMLHTTP procedure to import the info, this is what I understand it does but I may be wrong.

The second code downloads your info to a temp file.

You need to make changes to your web query to use these 2 codes, if we use the above code to import the Vic tab odds these are the changes we need to make.

Sheet1 has your variable info
Sheet2 has your imports

Code:


Sub Race()
Application.ScreenUpdating = False ‘ this stops the screen updating while it runs macro
Sheets("Sheet2").Select ‘ selects the sheet you want to import to
Range("A1:Z100").Select ‘ selects the range
Selection.ClearContents ’ clears the range
formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Sheet1").Range("F1").Value) ‘; this is the sheet and range where you have created the url as explained 
outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("sheet2").QueryTables.Add(Connection:= _
            "URL;" & ThisWorkbook.Path & "\temp.txt" _
            , Destination:=ThisWorkbook.Sheets("sheet2").Range("$A$1"))
    With temp_qt
        .Name = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """BetGrid_DGTableOne"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
‘this section will delete the temp file and remove all connections from the workbook
    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
    Sheets("Sheet1").Activate
    Application.ScreenUpdating = True ‘ reactivates screen updating
End Sub

I would be a good time to add a button to sheet1 that runs the macro, if you want this all on the one sheet that's fine just make sure you adjust the the first section that clears the sheet to start at say A3 and also adjust the Destination to A3 and also change sheet2 to sheet1