VBA to scrape web table - Error 91

[Chrican]

I'm trying to create a macro that will pull the fuel prices from this table into my spreadsheet. I've tried three separate 'tutorials', and none of them work - I get "Error 91: Object variable or With block variable not set" every time.

Here's the latest code I'm trying, and I've pointed out the line that debug highlights (in the middle, For Each line):

[COLOR=rgb(0, 0, 0)]Option Explicit
Sub GetTable()
    Dim html As MSHTML.HTMLDocument, hTable As Object, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set html = New MSHTML.HTMLDocument                  '<  VBE > Tools > References > Microsoft Scripting Runtime
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.petro-canada.ca/en/business/rack-prices", False
        .send
        html.body.innerHTML = .responseText
    End With
    Set hTable = html.querySelector("rack-pricing__table")
    Dim td As Object, tr As Object, th As Object, r As Long, c As Long
    For Each tr In hTable.getElementsByTagName("tr")                       '<<<< this is the line that gets highlighted by debug
        r = r + 1: c = 1
        For Each th In tr.getElementsByTagName("th")
            ws.Cells(r, c) = th.innerText
            c = c + 1
        Next
        For Each td In tr.getElementsByTagName("td")
            ws.Cells(r, c) = td.innerText
            c = c + 1
        Next
    Next
End Sub[/COLOR]

Any advice where I'm going wrong? I've enabled the MS Scripting Runtime tool.

btw I'm not sure how to remove that RGB line forum code, but its not part of the vba

 

[John_w]

Use instead:

    Set hTable = html.querySelector("table.rack-pricing__table")

I've enabled the MS Scripting Runtime tool.

That should be Microsoft HTML Object Library.

 

[Chrican]

That did it! Thanks a ton