[SOLVED] Making VBA scraped Data update automatically

Making VBA scraped Data update automatically

I Successfully wrote VBA to scrape Data from a webpage.

Since the Data updates regularly, I would like it to update in my Excel Document as well.

The Data I'm getting is the different Odds from All BookMakers listed.

I tried the .refreshperiod .refresh function for the Variable HTMLRow but that didn't work for some reason

Sub Scrape()
    For x = 1 To 5
        Worksheets("links").Select
        Worksheets("links").Activate
        mystr = "https://easyodds.com/tennis/challenger/santiago-chile/928271/match-result"
        mystr = Cells(x, 1)

        Dim XMLPage As New MSXML2.XMLHTTP60
        Dim HTMLDoc As New MSHTML.HTMLDocument
        Dim HTMLOdds As MSHTML.IHTMLElement
        Dim HTMLRow As Object
        Dim HTMLCell As MSHTML.IHTMLElement

        Dim RowNum As Long, ColNum As Integer
        XMLPage.Open "GET", mystr, False
        XMLPage.send

        Dim XMLResp As MSHTML.IHTMLElement
        HTMLDoc.body.innerHTML = XMLPage.responseText
        Set HTMLOdds = HTMLDoc.getElementById("betsTable")
        Worksheets.Add
        Cells.Select
        Selection.NumberFormat = "0.00"
        Range("A1").Value = mystr
        RowNum = 
        Set HTMLRow = HTMLOdds.getElementsByTagName("tbody")
        For Each HTMLRow In HTMLOdds.getElementsByTagName("tr")
            ColNum = 1
            For Each HTMLCell In HTMLRow.getElementsByTagName("Div")
                Cells(RowNum, ColNum) = HTMLCell.innerText
                ColNum = ColNum + 1
            Next HTMLCell
            RowNum = RowNum + 1
        Next HTMLRow
    Next x
End Sub

Solution

You could try calling your method with Application.OnTime Now + TimeValue("01:00:00"), "YourMethodName". This example would call the method once every hour but you would have to keep the file open.