JSON into excel from vb string

[andyt2005]

Good morning all,

Im in a real pickle and in need of some help from some experts out there, Im trying to get a web API through vb(done) then get the data from it into cells.
The data is returned in JSON, which is where im stuck.
Its basically a Q&A database, each question has a ref no assigned to it however im having issues getting what i need.
Heres a sample of the JSON:

{"meta":{"offset":0,"limit":50,"total":22},"data":[{"key":12094,"name":"How are you feeling?","locale":"en-US"},{"key":4214,"name":"How are you feeling about life?","locale":"en-US"},{"key":738,"name":"How clean was your house today?","locale":"en-US"},{"key":10224,"name":"How clean was your car?","locale":"en-US"},

So all i need is the "key" and the actual question. the VB store this JSON as a string once it has returned it. Its the storting the good from the bad i need help with. Note: Questions returned will and do change so counting lengh is no good for this.

Any ideas/help would be amazing!

Thanks

 

[Kyle123]

Or somewhat more succinctly:

Option Explicit
Sub test()

    Dim sc      As Object
    Dim obj     As Object
    Dim data()  As String
    Dim x       As Long
    Dim p       As Object
    Dim json    As String
    
    
    Set sc = CreateObject("MSScriptControl.ScriptControl")

    json = Sheets(1).Cells(1, 1).Value2

    sc.Language = "JScript"
    sc.AddCode "function getLength(jsonObj) { return jsonObj.data.length -1; } "
    Set obj = sc.Eval("(" & json & ")")

    ReDim data(1 To sc.Run("getLength", obj) + 1, 1 To 3)
    
    For x = LBound(data) To UBound(data)
        Set p = CallByName(obj.data, x - 1, VbGet)
        With p
            On Error Resume Next
                data(x, 1) = CallByName(p, "key", VbGet)
                data(x, 2) = CallByName(p, "name", VbGet)
                data(x, 3) = CallByName(p, "locale", VbGet)
            On Error GoTo 0
        End With
    Next x
    
    Sheets(1).Cells(2, 1).Resize(UBound(data), 3).Value2 = data

End Sub

 

[Peter_SSs]

... The JSON string is returned from the WEB API and stored in a VB Variable, Called ReturnedString. So the "end" user doesnt see the string. id like it to loop through the string and say " o heres a key, lets pull this and then pull the question with it" and then dump those two findings into cells, like we have done.

Like this? (Assumes columns A:B of the active sheet contain nothing that is required to be kept.)

Sub Get_Qns()
  Dim result, bits
  Dim j As Long
  
  'For testing
  Const ReturnedString As String = _
    "{""meta"":{""offset"":0,""limit"":50,""total"":22},""data"":[{""key"":12094,""name"":""How are you feeling?"",""locale"":""en-US""},{""key"":4214,""name"":""How are you feeling about life? "",""locale"":""en-US""}]}"
  
  bits = Split(ReturnedString, """key"":")
  ReDim result(1 To UBound(bits), 1 To 2)
  For j = 1 To UBound(bits)
    result(j, 1) = Split(bits(j), ",")(0)
    result(j, 2) = Split(bits(j), """")(3)
  Next j
  With Columns("A:B")
    .ClearContents
    .Resize(UBound(result)).Value = result
  End With
End Sub