Making VBA scraped Data update automatically

Question

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

so you're saying this code runs properly by itself, and you just need to have it auto-run? — ashleedawg, Mar 06 '18 at 15:45
Yeah the code runs properly for now at least, so I just need to make the Data that I've already scraped update like every min or every few seconds even. — Issy the kid, Mar 06 '18 at 15:56
the site will probably kick you off if you do it too often. Anyway, this question isn't about scraping at all, it's about how to run code on a timed interval, I'd suggested searching [so] (or elsewhere) for that. — ashleedawg, Mar 06 '18 at 16:04

score 1 · Accepted Answer · answered Mar 06 '18 at 15:48

1

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.

answered Mar 06 '18 at 15:48

Tofulicious

36
3

What do you mean by "mymethod" exactly. I'm sorry I'm very new to coding xD – Issy the kid Mar 06 '18 at 15:57
1

So you created a Sub called `Scrape()` that is what I call a name of your "method". Idk if this term is commonly used in VBA context so I'm not going into it further. Just try replacing `"YourMethodName"` with `"Scrape"`. This will make your whole Sub called `Scrape()` run again at your specified time. – Tofulicious Mar 06 '18 at 17:42

score 0 · Answer 2 · answered Mar 07 '18 at 09:20

In Module2 update the below code:

Sub Workbook_Open()
Call Module1.Scrape
End Sub

This code is triggered when you open the workfile. update the below code in your main program's first line

Sub Scrape()
Application.OnTime Now + TimeValue("0:01"), "Scrape"

it will auto execute the code for every minute, here you can change the time interval as your wish. This will execute in an infinite loop even you closed your worksheet.

Thank You.

Making VBA scraped Data update automatically

2 Answers2