Access VBA to parse json

Question

Can someone please help with an Access VBA code to parse the following json text? I need to retrieve the values for author, title, review, original_title, original_review, stars, iso, version, date, product, weight, and id and then loop to the next entry and next entry.

{
"total": 140,
"pages": 28,
"this_page": 1,
"reviews": [{
    "author": "DeveloperToDeveloper",
    "title": "Just Spectacular",
    "review": "Finally able to remove the ads! The description is hilarious!! Thanks!!!",
    "original_title": null,
    "original_review": null,
    "stars": "5.00",
    "iso": "US",
    "version": "1.2",
    "date": "2012-09-19T17:05:00",
    "product": 6567,
    "weight": 0,
    "id": "5561747L7xnbsMRu8UbPvy7A71Dv6A=="
}, {
    "author": null,
    "title": "Boat finder",
    "review": "Very good app for my use, regrefully does not Search vessel by name.",
    "original_title": null,
    "original_review": null,
    "stars": "5.00",
    "iso": "US",
    "version": "1.32",
    "date": "2012-08-10T21:15:56",
    "product": 5561747,
    "weight": 0,
    "id": "008bVy7L7xnbsMRu8UbPvy7xx1Dv6A=="
}, {
    "author": "Dito",
    "title": "Boat finder",
    "review": "Very good app for my use, regrefully does not Search vessel by name.",
    "original_title": null,
    "original_review": null,
    "stars": "5.00",
    "iso": "US",
    "version": "1.32",
    "date": "2012-08-10T21:15:56",
    "product": 5561,
    "weight": 0,
    "id": "5561747L7xhfgdbs09oyvy7A71Dv6A"
}, {
    "author": "KTM SX",
    "title": "À quand le deuxième épisode",
    "review": "Super une maj pour d'autre decore et autre",
    "original_title": null,
    "original_review": null,
    "stars": "5.00",
    "iso": "FR",
    "version": "1.2",
    "date": "2012-07-19T13:58:00",
    "product": 5858,
    "weight": 0,
    "id": "5561747L7xnbsMRu8UbP9s9s754gdt"
 }]

}

Possible duplicate of [Parsing JSON feed automatically into MS Access](http://stackoverflow.com/questions/30510570/parsing-json-feed-automatically-into-ms-access) — Gord Thompson, Feb 28 '16 at 18:42
My json has this tag { "total": 140, "pages": 28, "this_page": 1, "reviews": [ and I need to loop through the data — user3526768, Feb 28 '16 at 23:27
Also: http://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba — Tim Williams, Feb 28 '16 at 23:50

score 3 · Answer 1 · answered Feb 29 '16 at 00:06

Sub ParseJson()

    Dim j As String, sc As Object, num As Long, i

    j = Range("A1").Value 'I'm testing in excel...

    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"

    'evaluate the json to create a "response" object
    sc.ExecuteStatement "var response = eval((" & j & "));"

    'create a function to return us a piece of data
    '   based on the passed-in js fragment
    sc.ExecuteStatement "var f = function(s){return eval(s);};"

    'how many reviews ?
    num = sc.Eval("f('response.reviews.length')") '>> 4

    For i = 0 To num - 1
        Debug.Print "------- Review " & i & "---------------"
        Debug.Print sc.Eval("f('response.reviews[" & i & "].author')")
        Debug.Print sc.Eval("f('response.reviews[" & i & "].title')")
        'etc etc
    Next i

End Sub

i am actually connecting to an api to get this data. I have gotten the connection piece figured out in VBA. Parsing the data is a little problematic. I think Tim is leading me to a solution — user3526768, Feb 29 '16 at 13:44

Access VBA to parse json

1 Answers1

Linked