vba - Loop through nested array in parsed JSON string

Question

I am using the parser mentioned in: Is There a JSON Parser for VB6 / VBA? but I have issues getting the values of a nested array. Here is the JSON string that I have (response from a web-service, I validated it and it is valid JSON):

Dim stJsonResponse As String
stJsonResponse = {"stat":"ok","list":[{"url":["http://www.worldcat.org/oclc/69935068?referer=xid"],"publisher":"Tascabili Economici Newton","form":["BA"],"lang":"ita","city":"Milano","author":"Kahlil Gibran; org Tommaso Pisanti.","year":"1993","isbn":["9788879833172"],"title":"Aforismi sabbia e spuma","oclcnum":"69935068"]}]}

and here is the code I have so far:

Dim oJSON          As New JSON
Dim oJSONParsed    As Object

Set oJSONParsed = oJSON.parse(stJsonResponse)

For Each keyName In oJSONParsed.keys
    Select Case keyName
        Case "stat":
            Select Case oJSONParsed(keyName)
                Case "ok":
                    MsgBox "stat OK"
                Case "unknownId":
                    MsgBox "the request identifier looks like a valid ISBN number and unknown to xISBN service"
                Case "invalidId":
                    MsgBox "the request identifier is not a valid ISBN number"
                Case Else
                    'Case "unknownField": 'the request field is not supported
                    'Case "unknownFormat": 'the request format is not supported
                    'Case "unknownLibrary": 'the request library is not supported
                    'Case "unknownMethod": 'the request method is not supported
                    'Case "overlimit": 'the request is throttled, only header is returned
                    'Case "invalidAffiliateId": 'invalid affiliate id
                    'Case "invalidHash": 'invalid hash (subscription only)
                    'Case "invalidToken": 'invalid access token (subscription only)
                    MsgBox "Errore irreversiblie, Case interno su 'stat'"
            End Select
        Case "list":
            'Here I am stuck, I tried several options but I do not know how to proceed
            'Here latest attempt
            Dim temp As String
            temp = ""
            Dim colJSONArray   As Collection
            Set colJSONArray = oJSONParsed.Item("list")
            For Each key In colJSONArray
                '-------> How do I get the values here? <-------
                temp = temp + "Key: " + key + " Value: " + colJSONArray.Item("key") + vbNewLine
            Next
            MsgBox temp
        Case Else
            MsgBox "Errore irreversiblie"
    End Select
Next

The code colJSONArray.Item("key") gives me the error Invalid procedure call or argument
The code print colJSONArray.Count gives me 1
Any help appreciated.

Edit:Stupid me... cannot write key in ""!
'colJSONArray.Item(1)' works and gives me a Dictionary... I'll move from here.

score 0 · Answer 1 · answered Sep 07 '13 at 08:03

0

if colJSONArray.Item("key") is collection You need get first element of this colection.

...
dim tmp
For Each key In colJSONArray
     tmp = colJSONArray.Item("key")
     temp = temp + "Key: " + key + " Value: " + tmp(Lbound(tmp)) + vbNewLine
next key
....

answered Sep 07 '13 at 08:03

Piotr Marczewski

71
2

Thanks for the hint, but my probelm is that the code `colJSONArray.Item("key")` even in the immediate window gives me the error mentioned... what type shall the tmp variable be? – rodedo Sep 07 '13 at 08:22
do You try `colJSONArray(key)`? – Piotr Marczewski Sep 07 '13 at 08:36
As I wrote in my edit that was for sure the biggest mistke I did but it would also not help, because it only accepts digits, in my case only 1... – rodedo Sep 07 '13 at 08:46

vba - Loop through nested array in parsed JSON string

1 Answers1