Examples

[alastairgrant]

Could use some better examples.

e.g. How do you traverse keys and values once your have called ParseJson() which returns a VBA-Tools version of Dictionary?

[madmatthemad]

I've been using VBA-JSON just recently to import a (large & complicated) JSON data set into Excel. Here are some code snippets to help... To understand more about the particular JSON I'm trying to import - go to here: [https://mtgjson.com/] It is a fairly complex nested JSON structure.
Top level is a load of "sets" (which I've called "editions" in the code below), inside each set/edition there is an array of "cards" - each card has lots of data fields, some of which themselves are multiple arrays - for example "names" for cards with a "split" layout have more two names.

    Dim JSON As Dictionary
    Dim edition As Dictionary
    Dim card As Dictionary
    Dim name As Collection
    Dim JSONKeys as Object
    Dim name as Object

    Set JSON = JsonConverter.ParseJson(JsonText)  
' I'm assuming you know how to import the text from a file into the variable JsonText).  
' This is creating the top level dictionary that contains all the data. 

    JSONKeys = JSON.Keys  'This creates a simple array containing all the different Set/edition keys.

    For j = 0 To UBound(JSONKeys)   ' For-Next loop to go through each key of the top level JSON data
                                                         ' - i.e. each set.
        Set edition = JSON(JSONKeys(Z))
        For Each card In edition("cards")   'For-Next loop to go through each card in the set
            if card("layout") = "split" then     ' IF it is a split type card....
                 Set name = card("names") 
                 ws.Cells(j+2, 1).Value = name(1) & " and " name(2) 
           else
                 ws.Cells(j+2, 1).Value = card("name")
           end if
           ws.Cells(j+2, 1).Value = card("cmc")
            '...etc
        next Card
    next 

Hope this helps.

[mray190]

The example @madmatthemad showed above had some errors in it. Here is another example (a little less confusing as well)

    ' Get the JSON data from some external source
    ' I am retrieving data from firebase so therefore I call that function
    '  to get my JSON data in string format
    Dim result As String
    result = GetFromFirebase("items.json")

    ' Convert the string to JSON data
    Dim json As Dictionary
    Set json = JsonConverter.ParseJson(result)

    ' Iterate through each key
    Dim item As Dictionary
    For Each json_Key In json.Keys

        ' Create a new dictionary item representing the JSON data in the specific key
        Set item = json(json_Key)
        ' Print out the data - do whatever you need to do here
        MsgBox JsonConverter.ConvertToJson(item)

    Next

[erigoni]

It doesn't work with the following JSON: {"onlyKey": {"chunks": [{"length": 120.0, "kbps": 16, "filename": "onlyKey0110.vid"}, {"length": 120.0, "kbps": 16, "filename": "onlyKey9830.vid"}, {"length": 120.0, "kbps": 16, "filename": "onlyKey9950.vid"}], "baseURL": "http://myserver"}} I'm getting an Object with an unique item with value "onlyKey" but i can't access to internal data (chunks, filenames, etc)

I could fix in this way: Set JSONFull = ParseJson(http.responseText) i = 2 For Each Item In JSONFull For Each Chunk In JSONFull(Item) Select Case Chunk Case "chunks" Set Files = JSONFull(Item)(Chunk) For Each File In Files Sheets(1).Cells(i, 7).Value = File("length") Sheets(1).Cells(i, 8).Value = File("kbps") Sheets(1).Cells(i, 9).Value = File("filename") i = i + 1 Next Case "baseURL" Sheets(1).Cells(1, 10).Value = JSONFull(Item)(Chunk) End Select Next Next

[houghtonap]

The reason is that parseJson is returning an object, either a VBA collection for a JSON array or a Scripting Dictionary for a JSON object. In order to assign an object to a variable in VBA you need to use the Set statement. So add "set" in front of My_Json and it should correct the issue with your VBA code.

---

From: rustcat notifications@github.com Sent: Wednesday, October 9, 2019 2:47:03 PM To: VBA-tools/VBA-JSON VBA-JSON@noreply.github.com Cc: Subscribed subscribed@noreply.github.com Subject: Re: [VBA-tools/VBA-JSON] Examples (#49)

I'm stumped as a beginner with an error stating "Object variable or With block variable not set'. I'm sure the URL and access-token are correct because I can print out the strResult variable. Thanks for any help. [JSON_ERROR]https://user-images.githubusercontent.com/45665918/66510715-8ac13f00-eaa3-11e9-800f-591f945048b6.png

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/VBA-tools/VBA-JSON/issues/49?email_source=notifications&email_token=AIKTRA2RENDJ33ULDJFPR63QNYRKPA5CNFSM4DASYIJ2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEAY5NEA#issuecomment-540137104, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AIKTRA56XZURQ3QDHQDYBX3QNYRKPANCNFSM4DASYIJQ.