Type mismatch in Excel using Exceljson VBA



  • Referenced from https://codingislove.com/excel-json/

    I changed the Get request from http://jsonplaceholder.typicode.com/users, see the new VBA below. The request is returned correctly, as I put a msgbox with the response and it was good. However, Excel throws an error on the Sheets("NBAQUERY").Cells(i, 1).Value = Item("PLAYER_NAME") statement. The error is a runtime error 13 Type mismatch. What needs to be changed?

    Public Sub NBAexceljson()
    Dim http As Object, JSON As Object, i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://stats.nba.com/stats/leaguedashplayerstats?DateFrom=&DateTo=&GameScope=&GameSegment=&LastNGames=15&LeagueID=00&Location=&MeasureType=Advanced&Month=0&OpponentTeamID=0&Outcome=&PaceAdjust=N&PerMode=Totals&Period=0&PlayerExperience=&PlayerPosition=&PlusMinus=N&Rank=N&Season=2015-16&SeasonSegment=&SeasonType=Regular+Season&StarterBench=&VsConference=&VsDivision=", False
    http.send
    Set JSON = ParseJson(http.responseText)
    
    'MsgBox (http.responseText)
    
    i = 2
    For Each Item In JSON
    Sheets("NBAQUERY").Cells(i, 1).Value = Item("PLAYER_NAME")
    
    i = i + 1
    Next
    MsgBox ("complete")
    End Sub
    


  • I did a little more research and it appears that the output has a header field that describes all of the fields in each row, followed by data rows that include data only, no headers. below is the beginning of the response, including 1 row of data. How can I parse output like this?

    The response include the original request in JSON format, followed by a resultsets array that includes a headers field, followed by an array pr rowsets, 1 row per player.

    {"resource":"leaguedashplayerstats","parameters":{"MeasureType":"Advanced","PerMode":"Totals","PlusMinus":"N","PaceAdjust":"N","Rank":"N","LeagueID":"00","Season":"2015-16","SeasonType":"Regular Season","PORound":null,"Outcome":null,"Location":null,"Month":0,"SeasonSegment":null,"DateFrom":null,"DateTo":null,"OpponentTeamID":0,"VsConference":null,"VsDivision":null,"TeamID":null,"Conference":null,"Division":null,"GameSegment":null,"Period":0,"ShotClockRange":null,"LastNGames":15,"GameScope":null,"PlayerExperience":null,"PlayerPosition":null,"StarterBench":null,"DraftYear":null,"DraftPick":null,"College":null,"Country":null,"Height":null,"Weight":null},"resultSets":[{"name":"LeagueDashPlayerStats","headers":["PLAYER_ID","PLAYER_NAME","TEAM_ID","TEAM_ABBREVIATION","AGE","GP","W","L","W_PCT","MIN","OFF_RATING","DEF_RATING","NET_RATING","AST_PCT","AST_TO","AST_RATIO","OREB_PCT","DREB_PCT","REB_PCT","TM_TOV_PCT","EFG_PCT","TS_PCT","USG_PCT","PACE","PIE","FGM","FGA","FGM_PG","FGA_PG","FG_PCT","GP_RANK","W_RANK","L_RANK","W_PCT_RANK","MIN_RANK","OFF_RATING_RANK","DEF_RATING_RANK","NET_RATING_RANK","AST_PCT_RANK","AST_TO_RANK","AST_RATIO_RANK","OREB_PCT_RANK","DREB_PCT1","REB_PCT_RANK","TM_TOV_PCT_RANK","EFG_PCT_RANK","TS_PCT_RANK","USG_PCT_RANK","PACE_RANK","PIE_RANK","FGM_RANK","FGA_RANK","FGM_PG_RANK","FGA_PG_RANK","FG_PCT_RANK","CFID","CFPARAMS"],"rowSet":[[201166,"Aaron Brooks",1610612741,"CHI",31.0,14,8,6,0.571,16.4,98.1,108.6,-10.5,0.328,2.1,29.9,0.022,0.046,0.033,14.2,0.586,0.591,0.194,95.94,0.091,39,76,2.8,5.4,0.513,125,88,214,179,296,335,291,351,26,115,38,284,406,406,350,66,94,180,292,228,202,224,228,259,88,5,"201166,1610612741"],[203932,"Aaron Gordon",1610612753,"ORL",20.0,12,4,8,0.333,27.7,107.2,108.8,-1.6,0.106,3.71,15.2,0.087,0.179,0.131,4.1,0.469,0.493,0.186,99.78,0.099,55,127,4.6,10.6,0.433,211,263,321,293,128,140,295,225,216,23,181,84,133,110,39,278,294,208,120,187,132,120,112,101,238,5,"203932,1610612753"],
    

  • administrators

    @bskbal Hi, Here's the code to make it work for this data.

    Public Sub importNBAData()
    Dim http As Object, JSON As Object, i As Integer, headers As Object, rowSet As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://stats.nba.com/stats/leaguedashplayerstats?DateFrom=&DateTo=&GameScope=&GameSegment=&LastNGames=15&LeagueID=00&Location=&MeasureType=Advanced&Month=0&OpponentTeamID=0&Outcome=&PaceAdjust=N&PerMode=Totals&Period=0&PlayerExperience=&PlayerPosition=&PlusMinus=N&Rank=N&Season=2015-16&SeasonSegment=&SeasonType=Regular+Season&StarterBench=&VsConference=&VsDivision=", False
    http.send
    Set JSON = ParseJson(http.responseText)
    Set headers = JSON("resultSets")(1)("headers")
    Set rowSet = JSON("resultSets")(1)("rowSet")
    j = 1
    For Each Header In headers
    Sheets(1).Cells(1, j).Value = Header
    j = j + 1
    Next
    i = 2
    For Each Item In rowSet
    j = 1
    For Each detail In Item
    Sheets(1).Cells(i, j).Value = detail
    j = j + 1
    Next
    i = i + 1
    Next
    MsgBox ("complete")
    End Sub
    

    This code will import the entire data into sheet 1 along with headers. Please keep in mind that same code cannot be used for every data set. It has to be changed accordingly.

    Let me know if that helps :basketball:


guest-login-reply
 

reconnecting-message