Web-Scraping with ExcelVBA

Question

How would I pull the table data from Here?

I can see that each row is contained in a "team-name first" class. I would like to get the table into excel but using the from web option I can't see the table in the IE window. I think that VBA is the route that I need to take to get it. I tried some googling and youtube tutorials but I haven't had any success. Any help would be appreciated!

snip

**Edit Sorry I thought I attached my code. The problem is it is not loading the full page. So I think that is why I can't pull the data.

There should be a table showing here

Sub FetchNBADefense()

Dim IE As Object, obj As Object
Dim r As Long, c As Long, t As Long
Dim elemCollection As Object
Dim eRow As Long


Set IE = CreateObject("InternetExplorer.Application")

With IE

.Visible = True
.navigate ("https://stats.nba.com/teams/opponent/?sort=W&dir=-1")



While IE.readyState <> 4
    DoEvents
Wend

ThisWorkbook.Sheets("TeamDefenses").Range("A1:M60").ClearContents
Set elemColleciton = IE.document.getElementsByTagName("team-name first")
For t = 0 To (elemCollection.Length - 1)
    For r = 0 To (elemCollection(t).Rows.Cells.Length - 1)
        For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1)
        eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

        ThisWorkbook.Worksheets(1).Cells(eRow, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
        Next c
    Next r
Next t

End With
Range("A1:M60").Columns.AutoFit
'Clear memory
Set IE = Nothing

End Sub

***New code: What am I missing? I see that it is "resultSet" instead of "resultSets" but still getting and Runt-time error '424': Object required

Option Explicit

Public Sub FetchNBAplayerpts()

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Dim json As Object
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://stats.nba.com/stats/leagueLeaders?LeagueID=00&PerMode=PerGame&Scope=S&Season=2018-19&SeasonType=Regular+Season&StatCategory=PTS", False
    .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    .send
    Set json = JsonConverter.ParseJson(.responseText)("resultSet")(1)
End With
Dim headers As Object, header As Variant, headerOutput(), i As Long, rowInfo As Object, iRow As Object
Set headers = json("headers")
Set rowInfo = json("rowSet")
ReDim headerOutput(1 To headers.Count)
For Each header In headers
    i = i + 1
    headerOutput(i) = header
Next

Dim rowData(), r As Long, c As Long, Item As Variant
ReDim rowData(1 To rowInfo.Count, 1 To UBound(headerOutput))

For Each iRow In rowInfo
    r = r + 1: c = 1
    For Each Item In iRow
        rowData(r, c) = Item
    c = c + 1
    Next
Next

With ThisWorkbook.Worksheets("PlayerPts")
    .Cells(1, 1).Resize(1, UBound(headerOutput)) = headerOutput
    .Cells(2, 1).Resize(UBound(rowData, 1), UBound(rowData, 2)) = rowData
End With

End Sub

Answer 1

From discussion with @TylerH and @LuckyKleinschmidt it would seem that the page uses a javascript method, includes , that is not supported in IE. That may be why the page isn't fully rendering as a script isn't running. See here. The workaround would be to use indexOf method in the relevant script. I guess the developer(s) didn't worry about the small market share for IE.

Browser support:

If you happen to inspect in Chrome/Firefox dev tools, or using a web traffic monitoring tool such as fiddler, you will see that there is actually an XMLHTTP request sent to retrieve the data to a different source and that actually you can use that URL to issue an XMLTTP request. It is a much faster retrieval method than opening a browser so something of a win in this case. The response is a JSON response which can be handled with a JSON parser. I use JSONConverter.bas which you download and add to your project.

After adding the .bas from the above link to your project you then add a reference via VBE > Tools > References > Microsoft Scripting Runtime.

The JSON response has the following structure (Sample shown):

The { means a dictionary so you access by key, the [ means a collection so you access by index (or, For Each over as I do). "" means a string literal so you read as is. Test the data type and handle as required.

There is more info retrieved by this method than is visible on page.

Sample of output:

---

VBA:

Option Explicit    
Public Sub GetTable()       
    Dim json As Object
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://stats.nba.com/stats/leaguedashteamstats?Conference=&DateFrom=&DateTo=&Division=&GameScope=&GameSegment=&LastNGames=0&LeagueID=00&Location=&MeasureType=Opponent&Month=0&OpponentTeamID=0&Outcome=&PORound=0&PaceAdjust=N&PerMode=PerGame&Period=0&PlayerExperience=&PlayerPosition=&PlusMinus=N&Rank=N&Season=2018-19&SeasonSegment=&SeasonType=Regular+Season&ShotClockRange=&StarterBench=&TeamID=0&VsConference=&VsDivision=", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .send
        Set json = JsonConverter.ParseJson(.responseText)("resultSets")(1)
    End With
    Dim headers As Object, header As Variant, headerOutput(), i As Long, rowInfo As Object, iRow As Object
    Set headers = json("headers")
    Set rowInfo = json("rowSet")
    ReDim headerOutput(1 To headers.Count)
    For Each header In headers
        i = i + 1
        headerOutput(i) = header
    Next

    Dim rowData(), r As Long, c As Long, item As Variant
    ReDim rowData(1 To rowInfo.Count, 1 To UBound(headerOutput))

    For Each iRow In rowInfo
        r = r + 1: c = 1
        For Each item In iRow
            rowData(r, c) = item
            c = c + 1
        Next
    Next

    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(1, 1).Resize(1, UBound(headerOutput)) = headerOutput
        .Cells(2, 1).Resize(UBound(rowData, 1), UBound(rowData, 2)) = rowData
    End With

End Sub

---

The XHR request in dev tools (network tab):