title: Using VBA-JSON to pull from API "error: object does not support this property or method here"?
body: <p>Trying to pull specific data from an API using VBA-JSON. Get the error: object does not support this property or method here | For Each key In json.Keys. Trying to figure out why.</p>
  
  <p>When I try "For Each Key in json.Key" I get the error code in the title. I know this is because defender_list is an array so I need to define an object, but I'm struggling on how to create a For statement for the object (if that's what I need to do). I took the For each Key part out because I know it is wrong. </p>
  
  <pre><code>Option Explicit
  
  Public Sub WriteOutBattleInfo()
      Dim headers(), r As Long, i As Long, json As Object, key As Variant, ws As Worksheet, defenderList As Object, monsterInfo As Object
      Set ws = ThisWorkbook.Worksheets("Sheet3")
      headers = Array("Username", "Avg BP", "Avg Level", "Opp. Address", "Player ID", "Catch Number", "Monster ID", "Type 1", "Type 2", "Gason Y/N", "Ancestor 1", "Ancestor 2", "Ancestor 3", "Class ID", "Total Level", "Exp", "HP", "PA", "PD", "SA", "SD", "SPD", "Total BP")
  
      With CreateObject("MSXML2.XMLHTTP")
          .Open "GET", "https://www.etheremon.com/api/ema_battle/get_rank_castles?v=8588587&amp;trainer_address=0x2fef65e4d69a38bf0dd074079f367cdf176ec0de", False
          .Send
          Set json = JsonConverter.ParseJson(.ResponseText)("data")("defender_list") 'dictionary of dictionaries
      End With
      r = 2
      ws.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
  </code></pre>
  
  <p>Edit: tried this</p>
  
  <pre><code>Dim obj As Variant
  Dim monsterInfo, obj2
  Dim types, obj3
  Dim ancestors, obj4
  Dim totalBattleStats, obj5
  
  For Each obj In json
      Debug.Print obj("username")
      Debug.Print obj("avg_bp")
      Debug.Print obj("avg_level")
      Debug.Print obj("player_id")
  
      Set monsterInfo = obj("monster_info")
      For Each obj2 In monsterInfo
          Debug.Print obj2("create_index")
          Debug.Print obj2("monster_id")
  
          Set types = obj("types")
          Debug.Print obj3("types")
  
          Debug.Print obj2("is_gason")
  
          Set ancestors = obj("ancestors")
          Debug.Print obj4("ancestors")
  
          Debug.Print obj2("class_ID")
          Debug.Print obj2("total_level")
          Debug.Print obj2("exp")
  
          Set totalBattleStats = obj("total_battle_stats")
          Debug.Print obj5("total_battle_stats")
  
          Debug.Print obj2("total_bp")
  
  Next obj
  
        Cells.Select
        Selection.Columns.AutoFit
  
  
  
    End Sub
  </code></pre>
  
  <p>I want to pull specifically from "defender_list". Each "username" has 6 mons associated with it. Most importantly I want the stats from each of those mons, but I've listed everything I need pulled in headers=. The variable names for the code are "username", "avg_bp", "avg_level", "player_id" and then within "monster_info" are "create_index", "monster"id" "types" (array type1, type2), "is_gason", "ancestors" (array ancestor 1,2,3), "class_id", "total_level", "exp", total_battle_stats (array hp, pa, pd, sa, sd, spd), "total_bp".</p>
  
  <p><a href="https://i.stack.imgur.com/mxiLI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mxiLI.png" alt="json sample"></a></p>
  
  <p>The expected output is:</p>
  
  <p><a href="https://i.imgur.com/HnqAriW.png" rel="nofollow noreferrer"><img src="https://i.imgur.com/HnqAriW.png" alt="output"></a></p>
  
  <p>Where:<br>
  Username - "username"<br>
  Avg BP - "avg_bp"<br>
  Avg Level - "avg_level"<br>
  Player ID - "player_id"<br>
  Catch Number - "create_index"<br>
  Monster ID - "monster"id"<br>
  Type 1, Type 2 - "types" (array type1, type2)<br>
  Gason - "is_gason"<br>
  Ancestor 1, Ancestor 2, Ancestor 3 - "ancestors" (array ancestor 1,2,3)<br>
  Class ID - "class_id"<br>
  Total Level - "total_level"<br>
  Exp - "exp"<br>
  HP, PA, PD, SA, SD, SPD - "total_battle_stats" (array hp, pa, pd, sa, sd, spd)<br>
  Total BP - "total_bp"    </p>
  
  <p>Everything after player_ID in that list is mon specific. Each username has 6 mons which would mean 6 rows per username. The things before player_ID could just be repeated for rows 1-6 that doesn't matter.</p>