Parsing complex JSON Data using VBA JSON

Question

Im trying to parse data from a Json File using VBA Json which is like this :

{
    "a131331": {
        "time" : "10:28 a.m.",
        "title" : "first"
    },
    "b319810" : {
        "time" : "11:14 a.m.",
        "title" : "third"
    },
    ...
}

I want to replace the key a131331 with 1, b319810 with 2 and so on in the first column of a spreadsheet, and place the other corresponding data in the next columns. So my excel sheet would look like:

ID|Time |Title
1 |10.28|first
2 |11.14|third

Im using this code in VBA :

Public Sub ExcelJson()
    Dim FSO As New FileSystemObject
    Dim JsonTS As TextStream
    Set JsonTS = FSO.OpenTextFile("M:\Ds Downloads New\test.json", ForReading)
    JsonText = JsonTS.ReadAll
    JsonTS.Close
    Set JSON = ParseJson(JsonText)

    i = 2

    For Each Item In JSON

        Sheets(1).Cells(i, 1).Value = Item("time")
        Sheets(1).Cells(i, 2).Value = Item("title")
        i = i + 1

    Next

    MsgBox ("complete")

End Sub

I understand I can create a column with the number range starting from 1 for the ID column. But how do I parse those objects a131331 and b319810?

1. You'd need to show us your ParseJSon function. 2. Don't understand why you need to parse them if you don't need those key ids??? — MacroMarc
– MacroMarc, Commented Jan 31, 2017 at 18:56
Just store i in your cell? Steets(1).Cells(i,1).Value = i - 1 for the ID column, Sheets(1).Cells(i, 2).Value = Item("time"), etc. — Grisha Levit
– Grisha Levit, Commented Jan 31, 2017 at 19:02
Try this solution, JSON.Parse and JSON.ToArray, then you can process the array or output it to worksheet. — omegastripes
– omegastripes, Commented Mar 7, 2017 at 8:45

Tim Williams · Accepted Answer · 2017-01-31 21:56:08Z

1

Your JSON object is a Scripting Dictionary, so you can either iterate over the Keys, or over the Values.

Iterating over the Keys:

Dim Json As Object, k

'getting the JSON content from a worksheet for testing purposes...
Set Json = JsonConverter.ParseJson(Sheet1.Range("B1").Value)

For Each k In Json.Keys

    Debug.Print k, Json(k)("time"), Json(k)("title")

Next k

Output:

a131331       10:28 a.m.    first
b319810       11:14 a.m.    third

answered Jan 31, 2017 at 21:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

dhwanilshah Over a year ago

This is exactly what I was looking for. Thank you so much Tim !

Collectives™ on Stack Overflow

Parsing complex JSON Data using VBA JSON

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related