How can I get data in json array with vba?

Question

How can I do a loop to get data from projects _data like code, company_name,...?

JSON looks like:

   {"ERR":"0",
    "error_code":"",
    "error_message":"",
    "projects_data":{"173":
          {"id":"173",
            "code":"PRJ-0163",
            "company_id":"27",
            "company_name":"Associa\u00e7\u00e3o - Interven\u00e7\u00e3o de excel\u00eancia no sector Agro-Alimentar",
            "company_logo":"",
            "name":"PROJECT NAME",
            "reference":"20190127",
            "description":"France,Germany",
            "allowoutsiders":"1",
            "barcode":"",
            "stages_data":{"648":

...

<
    ERR: "0", error_code: "", error_message: "",…}
    ERR: "0"
    error_code: ""
    error_message: ""
    projects_data: {25: {id: "25", code: "PRJ-0015", company_id: "10",…},…}
    25: {id: "25", code: "PRJ-0015", company_id: "10",…}
    34: {id: "34", code: "PRJ-0024", company_id: "15", company_name: "Ceu - Indústria e Comércio, S.A.",…}
    44: {id: "44", code: "PRJ-0034", company_id: "3", company_name: "Roca, Turismo e Indústria S.A.",…}
    49: {id: "49", code: "PRJ-0039", company_id: "33",…}
    50: {id: "50", code: "PRJ-0040", company_id: "33",…}
    51: {id: "51", code: "PRJ-0041", company_id: "33",…}
    52: {id: "52", code: "PRJ-0042", company_id: "33",…}
    67: {id: "67", code: "PRJ-0057", company_id: "3", company_name: "Roca, Turismo e Indústria S.A.",…}
    77: {id: "77", code: "PRJ-0067", company_id: "235", company_name: "Paper UK Ltd", company_logo: "",…}
    78: {id: "78", code: "PRJ-0068", company_id: "234",…}
    80: {id: "80", code: "PRJ-0070", company_id: "238", company_name: "Azeitona...
>

Sub Macro1()


Dim jsonText As String
Dim JsonObject As Object, Item As Object

Set hreq2 = CreateObject("MSXML2.XMLHTTP")
    With hreq2
        .Open "GET", "https://192.111.0.xxx/ws/mobile/index.php?method=getProjects&SESSION_KEY=SOAPP5d10b8a06bc6f5.xxxx&action=all"
       .Send
    End With  

 Dim RESPONSE2 As String
     RESPONSE2 = "[" & hreq2.ResponseText & "]"
     Range("a1").Value = RESPONSE2



    Set JsonObject = JsonConverter.ParseJson(RESPONSE2)


    i = 1

    Dim keyCurr As String

    For Each Item In JsonObject
    ActiveSheet.Cells(i + 2, 2).Value = Item.Keys
    For Each curr In Item
        If curr = "projects_data" Then
        Range("A8").Value = JsonObject(i)(curr)("code")
        Range("A9").Value = JsonObject(i)(curr)("company_id")
        i = i + 1
        Else
        End If
  Next curr
  Next
  End Sub

"I get into "projects_data" but the return is empty. Can you help me? "

Would help to show the complete/unedited JSON (or at least not cut off the start) — Tim Williams, Jun 24 '19 at 15:11
In addition to @TimWilliams request for a correctly formatted (subset) of your JSON data, you may find some useful information in [this answer](https://stackoverflow.com/a/46245469/4717755) that may help. — PeterT, Jun 24 '19 at 15:14
Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: [MCVE] — QHarr, Jun 24 '19 at 19:31
Hello! This is the beggining of the json data, I was trying to show you in a diferent way because it is huge. Sorry, I'm a beginner... .For now I want to get the projects information like code, company name,... Thanks{"ERR":"0","error_code":"","error_message":"","projects_data":{"173":{"id":"173","code":"PRJ-0163","company_id":"27","company_name":"Associa\u00e7\u00e3o - Interven\u00e7\u00e3o de excel\u00eancia no sector Agro-Alimentar","company_logo":"","name":"PROJECT NAME","reference":"20190127","description":"France,Germany","allowoutsiders":"1","barcode":"","stages_data":{"648": — Susana Mendonca, Jun 25 '19 at 16:33
Please *edit your question* when adding clarifying code/content/comments... — Tim Williams, Jun 26 '19 at 00:23

Tim Williams · Accepted Answer · 2019-06-27T15:21:14.123

0

Not really a large enough example of your actual JSON, but for example:

Dim JsonObject As Object, projects As Object, project As Object, k, sk, v, stages

Set JsonObject = JsonConverter.ParseJson(hreq2.ResponseText) 'don't add []

Set projects = JsonObject("projects_data") 'projects is a Dictionary object
For Each k In projects.keys
    Debug.Print "----------Project " & k
    Set project = projects(k) 'another Dictionary
    'loop over some of the dictionary keys
    For Each v In Array("id", "code", "name", "reference")
        Debug.Print v, project(v)
    Next v

    Set stages = project("stages_data")
    for each sk in stages.keys
        debug.print "stage", sk, stages(sk)
    next sk  

Next k

edited Jun 27 '19 at 15:21

answered Jun 26 '19 at 00:33

Tim Williams

154,628
8
97
125

Worked perfect! Only need one more question, How to get the data of "stages_data"? – Susana Mendonca Jun 27 '19 at 15:11
In "stages_data" there is more information that I need to get example: "stages_data":{"648":{"fase_id":"648","name":"Forma\u00e7\u00e3o, assessoria e coaching","barcode":"F648","activities_data":{"1966":{"activity_id":"1966","description":"Arranque,....I tried but it returned empty. See what I did in the code please. – Susana Mendonca Jul 01 '19 at 10:16
If you have some code you want to show, then please edit your question, instead of my reply. – Tim Williams Jul 05 '19 at 05:53

How can I get data in json array with vba?

1 Answers1