Difficulty referencing Object



  • Re: Type mismatch in Excel using Exceljson VBA

    The json is below. How can I reference the fields and values in parameters? Also, is there documentation on how to do this? Thanks again for your help

    {"resource":"leaguedashplayerstats","parameters":{"MeasureType":"Base","PerMode":"Totals","PlusMinus":"N","PaceAdjust":"N","Rank":"N","LeagueID":null,"Season":"2016-17","SeasonType":"Regular Season","PORound":null,"Outcome":null,"Location":null,"Month":0,"SeasonSegment":null,"DateFrom":null,"DateTo":null,"OpponentTeamID":0,"VsConference":null,"VsDivision":null,"TeamID":0,"Conference":null,"Division":null,"GameSegment":null,"Period":0,"ShotClockRange":null,"LastNGames":0,"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","FGM","FGA","FG_PCT","FG3M","FG3A","FG3_PCT","FTM","FTA","FT_PCT","OREB","DREB","REB","AST","TOV","STL","BLK","BLKA","PF","PFD","PTS","PLUS_MINUS","DD2","TD3","GP_RANK","W_RANK","L_RANK","W_PCT_RANK","MIN_RANK","FGM_RANK","FGA_RANK","FG_PCT_RANK","FG3M_RANK","FG3A_RANK","FG3_PCT_RANK","FTM_RANK","FTA_RANK","FT_PCT_RANK","OREB_RANK","DREB_RANK","REB_RANK","AST_RANK","TOV_RANK","STL_RANK","BLK_RANK","BLKA_RANK","PF_RANK","PFD_RANK","PTS_RANK","PLUS_MINUS_RANK","DD2_RANK","TD3_RANK","CFID","CFPARAMS"],"rowSet":[[1627773,"AJ Hammons",1610612742,"DAL",24.0,14,3,11,0.214,64.785,6,18,0.333,3,4,0.75,2,10,0.2,4,15,19,2,1,0,6,4,10,8,17,10,0,0,334,379,233,418,383,385,385,390,293,328,4,380,325,417,319,346,345,397,404,409,206,147,81,340,382,151,157,11,5,"1627773,1610612742"],[201166,"Aaron Brooks",1610612754,"IND",31.0,25,13,12,0.52,373.64666666666665,53,135,0.393,18,56,0.321,15,17,0.882,8,20,28,55,31,15,4,12,43,23,139,10,0,0,158,115,262,175,253,239,214,309,163,160,220,252,280,63,264,324,322,107,145,170,247,302,249,249,245,151,157,11,5,"201166,1610612754"]]}]}
    

  • administrators

    @bskbal I assume that you want to extract the fields and values in parameters. You can do that using the following code.

    Public Sub importParameters()
    Dim http As Object, JSON As Object, i As Integer, parameters As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://api.myjson.com/bins/r00z9", False
    http.send
    Set JSON = ParseJson(http.responseText)
    Set parameters = JSON("parameters")
    i = 1
    For Each parameterField In parameters.Keys()
    Sheets(1).Cells(i, 1).Value = parameterField
    Sheets(1).Cells(i, 2).Value = parameters(parameterField)
    i = i + 1
    Next
    End Sub
    

    Above code will extract all the fields to column A and all the values to column B.
    I've saved your JSON here - https://api.myjson.com/bins/r00z9 for testing.

    I'm setting the parameters to an object named parameters first. Now parameters is a dictionary object. Now I loop through the dictionary to get its keys and values.

    There isn't really any documentation for this. You have to understand the concept of objects and arrays and how JSON is structured. Once you understand that, you will be able to handle any type of JSON, I'm writing a detailed tutorial about JSON structure. Stay tuned for that!

    Also, Please post Reply in the same topic instead of starting a new topic. (Reply instead of Reply as topic)


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.