Using VBA-JSON to pull from API "error: object does not support this property or method here"?

Question

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.

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.

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&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

Edit: tried this

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

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".

The expected output is:

Where:
Username - "username"
Avg BP - "avg_bp"
Avg Level - "avg_level"
Player ID - "player_id"
Catch Number - "create_index"
Monster ID - "monster"id"
Type 1, Type 2 - "types" (array type1, type2)
Gason - "is_gason"
Ancestor 1, Ancestor 2, Ancestor 3 - "ancestors" (array ancestor 1,2,3)
Class ID - "class_id"
Total Level - "total_level"
Exp - "exp"
HP, PA, PD, SA, SD, SPD - "total_battle_stats" (array hp, pa, pd, sa, sd, spd)
Total BP - "total_bp"

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.

json here will be a Collection object,, so you can use For Each obj In json to loop over it, and within that loop obj will represent a dictionary with keys "username", "avg_bp" etc. — Tim Williams
– Tim Williams, Commented Feb 19, 2019 at 3:46
@timwilliams and how do I define the obj? With my current code when I do For Each Obj in json I get "Obj not defined". Also, should I do Obj in json.defenderList? — EB_Crypto
– EB_Crypto, Commented Feb 19, 2019 at 3:50
What is the expected output? Please populate the table manually at least for one item and add the example to the question. — omegastripes
– omegastripes, Commented Feb 19, 2019 at 5:59
@omegastripes imgur.com/a/BLH0m1o does this work? this is exactly what i'm looking for — EB_Crypto
– EB_Crypto, Commented Feb 19, 2019 at 6:44

QHarr · Accepted Answer · 2019-02-19 12:13:23Z

1

You have a few confounding factors.

1) You have nested monsters within each player. You need to account for this and duplicate initial row info for these lines.

2) Due to your headers you have assumed the json structure is both regular and contains the same number of items/keys in each object within the json. This is not the case, for example, MonsterId 47023 has only one Type value and no ancestors. I use some hard coded values to set a loop based on your headers i.e. that there should be 2 Type values and 3 ancestor values. I wrap this in an On Error Resume Next On Error GoTo O to suppress the resultant error when attempting to access a non existant item. Thus a blank get entered.

Example outlier:

VBA:

Option Explicit   
Public Sub WriteOutBattleInfo()
    Dim headers(), json As Object, key As Variant, ws As Worksheet
    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&trainer_address=0x2fef65e4d69a38bf0dd074079f367cdf176ec0de", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)("data")("defender_list") 'Collection of dictionaries
    End With
    Dim dict As Object, c As Long, r As Long, item As Object, key2 As Variant, results(), item2 As Long
    ReDim results(1 To json.Count * json(1)("monster_info").Count, 1 To UBound(headers) + 1) '<== +1 here if can establish what catch number is

    Dim j As Long
    For Each dict In json
        r = r + 1: c = 1
        For Each key In dict.keys
            Select Case key
            Case "username", "avg_bp", "avg_level", "address", "player_id"
                results(r, c) = dict(key)
                c = c + 1
            Case "monster_info"
                Dim monsterNumber As Long, temp(1 To 5)
                monsterNumber = 1
                For Each item In dict(key)       'collection of dictionaries               
                    If monsterNumber = 1 Then
                        For j = 1 To 5
                            temp(j) = results(r, j)
                        Next
                    Else
                        r = r + 1
                        For j = 1 To 5
                            results(r, j) = temp(j)
                        Next
                    End If
                    For Each key2 In item.keys
                        Select Case key2
                        Case "create_index", "monster_id", "is_gason", "class_id", "total_level", "exp", "total_bp"
                            results(r, c) = item(key2)
                            c = c + 1
                            Case "types" '<==expecting 2. Can get 1. Maybe different count?
                            For item2 = 1 To 2
                                On Error Resume Next
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                                On Error GoTo 0
                            Next
                            Case "ancestors" '<== expecting 3. Can get 0. Maybe different number
                            For item2 = 1 To 3
                                On Error Resume Next
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                                On Error GoTo 0
                            Next
                            Case "battle_stats"
                            For item2 = 1 To item(key2).Count
                                results(r, c) = item(key2).item(item2)
                                c = c + 1
                            Next
                        End Select
                    Next
                    c = 6: monsterNumber = monsterNumber + 1
                Next item
            End Select
        Next
    Next
    ws.Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
    ws.Cells(2, 1).Resize(UBound(results, 1), UBound(results, 2)) = results
End Sub

Sample output:

edited Feb 19, 2019 at 12:13

answered Feb 19, 2019 at 10:23

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

EB_Crypto Over a year ago

You are incredible. Exactly what I needed. Thank you so much. Works perfect

QHarr Over a year ago

I have added catch number in. Apologies. I hadn't seen comments.

EB_Crypto Over a year ago

To get the trainer info before I just need to change defender_list tom player_info and monster_info to monsters, right? Oh and also remove two cases from first part

QHarr Over a year ago

no (if just after single item trainer from inside monster).... you add trainer info to the headers before catch and add "trainer" before "create_index" in Case "create_index", "monster_id", "is_gason", "class_id", "total_level", "exp", "total_bp"

QHarr Over a year ago

or use Set json = JsonConverter.ParseJson(.responseText)("data")("player_info") which returns a dictionary then loop the keys of the dictionary. (if after the item before monsters.

|

Tim Williams · Accepted Answer · 2019-02-19 04:52:43Z

1

This should work (for example):

Dim obj 'as variant
Dim mInfo, obj2
For Each obj in json
    Debug.Print obj("username")
    Set mInfo = obj("monster_info")
    For Each obj2 in mInfo
        Debug.Print obj2("trainer")
        Debug.Print obj2("monster_id")
    Next obj2
Next obj

edited Feb 19, 2019 at 4:52

answered Feb 19, 2019 at 3:53

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

5 Comments

EB_Crypto Over a year ago

Something like this? I still get an error. Then I need to reference the array that is monster_info and pull the objects within that. Could you give me any guidance from here? (edited to put the code I'm asking about in the code section)

Tim Williams Over a year ago

"still get an error" is not a very useful description of the problem you're having. If you need to show code then (1) update your question to add it (2) be specific about errors and note which line they occur on

EB_Crypto Over a year ago

apologies, i get "compile error: expected end with" at End Sub

Tim Williams Over a year ago

Well you have no "End With" so you will need to fix that. And I'm not sure what you're doing here: json(obj)("username") Why not just try what I posted? Start simple and get that working first.

EB_Crypto Over a year ago

okay, i got it to print everything before "monster_info" which is an array within the array and the main data I need. Just edited the code

Collectives™ on Stack Overflow

Using VBA-JSON to pull from API "error: object does not support this property or method here"?

2 Answers 2

9 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related