Pfthb

im trying right now parsing mutliple JSONs in VBA in Excel. With Google and SO i managed to parse Multiple JSONs in a Format like this:

 "name": "Starker Geschmeidiger Holz-Langbogen des Feuers",
 "description": "",
 "type": "Weapon",
 "level": 44,
 "rarity": "Masterwork",
 "vendor_value": 120,
 "default_skin": 3942,
 "game_types": [
 "Activity",
 "Wvw",
 "Dungeon",
 "Pve"
 ],
 "flags": [
 "SoulBindOnUse"
 ],
 "restrictions": ,
 "id": 28445,
 "chat_link": "[&AgEdbwAA]",
 "icon": "https://render.guildwars2.com/file/C6110F52DF5AFE0F00A56F9E143E9732176DDDE9/65015.png",
 "details": 
 "type": "LongBow",
 "damage_type": "Physical",
 "min_power": 385,
 "max_power": 452,
 "defense": 0,
 "infusion_slots": ,
 "infix_upgrade": 
 "id": 142,
 "attributes": [
 
 "attribute": "Power",
 "modifier": 85
 ,
 
 "attribute": "Precision",
 "modifier": 61
 
 ]
 ,
 "suffix_item_id": 24547,
 "secondary_suffix_item_id": ""
 

I do it like this:

Private Function Get_Name(id As Integer) As String

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "https://api.guildwars2.com/v2/items/" & id & "?lang=de"

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sGetResult = httpObject.responseText

 Dim oJSON As Object
 Set oJSON = JsonConverter.ParseJson(sGetResult)

 For Each sItem In oJSON
 If sItem = "name" Then
 Get_Name = oJSON(sItem)
 End If
 Next

End Function

That works fine, but i have one JSON i get from the API, that has a different Format and i dont manage to get this to work too.. It hast the following Format:

[
 
 "id": 12134,
 "category": 5,
 "count": 204
 ,
 
 "id": 12238,
 "category": 5,
 "count": 150
 ,
 
 "id": 12147,
 "category": 5,
 "count": 146
 ,
 
 "id": 12142,
 "category": 5,
 "count": 215
 ,
....
]

Thats my Try so Far:

Private Function Get_Anzahl_Im_Lager(id As Integer) As Integer

Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 sURL = "https://api.guildwars2.com/v2/account/materials?access_token=" & Tabelle2.Cells(1, 7)

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sGetResult = httpObject.responseText

 MsgBox sGetResult

 Dim oJSON As Collection
 Set oJSON = JsonConverter.ParseJson(sGetResult)

 MsgBox oJSON

 For Each sItem In oJSON
 'If oJSON(sItem)("id") = id Then
 ' Get_Anzahl_Im_Lager = oJSON(sItem)("count")
 ' End If
 Get_Anzahl_Im_Lager = sItem
 Exit Function
 Next

End Function

Problem is,according to the Debugger it parses the Array, but i just get an Empty Object back here, oJSON is empty, while sGetResult hast the JSON Data in it.

Any Solutions?

Made it.. Sometimes i should just start thinking from a new Point on :D

Private Function Get_Anzahl_Im_Lager(id As Integer) As Integer

 Dim httpObject As Object
 Set httpObject = CreateObject("MSXML2.XMLHTTP")

 If Not IsEmpty(Tabelle2.Cells(1, 7)) Then
 sURL = "https://api.guildwars2.com/v2/account/materials?access_token=" & Tabelle2.Cells(1, 7)
 Else
 Exit Function
 End If

 sRequest = sURL
 httpObject.Open "GET", sRequest, False
 httpObject.send
 sGetResult = httpObject.responseText

 Dim oJSON As Object
 Set oJSON = JsonConverter.ParseJson(sGetResult)

 Dim sItem, cnt&
 For Each sItem In oJSON
 cnt = cnt + 1
 If oJSON(cnt)("id") = id Then
 Get_Anzahl_Im_Lager = oJSON(cnt)("count")
 Exit Function
 End If
 Next

End Function

The JSON objects are of two different types. One is dictionary and one is a collection. Use TypeName to determine which you are getting from the responseText and handle as required e.g.

Dim item As Long, oJSON As Object
Set oJSON = JsonConverter.ParseJson(sGetResult)

Select Case TypeName(oJSON)

Case "Collection"

 For Each item In json
 Debug.Print item("count")
 Next

Case "Dictionary"

 Debug.Print json("name")

End Select