0

I am new to Macro creation. I have a json like below with one master key and multiple sub values..

{"uuid":"9BFD4668B3EE5F09E053512C48A5B19E","code":"PROCDHD","name":"PROD Holdings","customerSegment":"BC","marketGroup":"BC","corporatePartnerType":"USUAL CUST","divisions":[{"uuid":"9BFD4669D5B45F09E053512C48A5B19E","code":"PRADNTCRP","name":"Kim International","shortName":"PRAD International","remarks":"A Luxury Pearls & Jewelry Company","businessPartners":[{"uuid":"9BFD466743A85F09E053512C48A5B19E","AT1Code":"CTOII011","name":"TIMI INTERNATIONAL CORPORATION","address":{"uuid":"9BFD4659FC7A5F09E053512C48A5B19E","addressLine1":"11000 MONARCHI ST","city":"GARDEN CLOVE","zip":"94221","countryCode":"IN"}}]},{"uuid":"9BFD4669AF8F5F09E053512C48A5B19E","code":"VRISHKA","name":"Vrisksha Holdings","shortName":"Vrshco Holdings","remarks":"Sells Packaging, jewells For jewellery Industry","businessPartners":[{"uuid":"9EE6E38AE8125405E053512C48A5498C","AT1Code":"CSKUS008","name":"Vriskha HOLDINGS","address":{"uuid":"9ED8CA9DC4D670AAE053522C48A559BF","addressLine1":"11958 MONARCHI STREET","city":"GARDEN CLOVE","zip":"94221","countryCode":"IN"}}]}]}

9BFD4668B3EE5F09E053512C48A5B19E - Master key,

9BFD4669D5B45F09E053512C48A5B19E, 9BFD4669AF8F5F09E053512C48A5B19E - sub key rows (should be displayed as 2 rows in excel for one key)

I managed to get the first sub key alone, but second key is not being displayed,

my code:

Public Sub exceljson_Type1()
    Dim https As Object, Json As Object, i As Integer
    Dim Item As Variant
    Set https = CreateObject("MSXML2.XMLHTTP")
    https.Open "GET", "file:///C:/Users/Desktop/Tasks/JSON/sample.json", False
    https.Send
    Set Json = JsonConverter.ParseJson(https.responseText)
    i = 3
    For Each Item In Json
    Sheets(1).Cells(i, 1).Value = Item("uuid")
    Sheets(1).Cells(i, 2).Value = Item("code")
    Sheets(1).Cells(i, 3).Value = Item("customerSegment")
    Sheets(1).Cells(i, 4).Value = Item("marketGroup")
    Sheets(1).Cells(i, 5).Value = Item("corporatePartnerType")
    Sheets(1).Cells(i, 6).Value = Item("divisions")("uuid")
    Sheets(1).Cells(i, 7).Value = Item("divisions")("name")
    Sheets(1).Cells(i, 8).Value = Item("divisions")("name")
    Sheets(1).Cells(i, 9).Value = Item("divisions")("shortName")
    Sheets(1).Cells(i, 10).Value = Item("divisions")("remarks")
    Sheets(1).Cells(i, 11).Value = Item("divisions")("businessPartners")("uuid")
    Sheets(1).Cells(i, 12).Value = Item("divisions")("businessPartners")("cw1Code")
    Sheets(1).Cells(i, 13).Value = Item("divisions")("businessPartners")("name")
    Sheets(1).Cells(i, 14).Value = Item("divisions")("businessPartners")("address")("uuid")
    Sheets(1).Cells(i, 15).Value = Item("divisions")("businessPartners")("address")("addressLine1")
    Sheets(1).Cells(i, 16).Value = Item("divisions")("businessPartners")("address")("city")
    Sheets(1).Cells(i, 17).Value = Item("divisions")("businessPartners")("address")("zip")
    Sheets(1).Cells(i, 18).Value = Item("divisions")("businessPartners")("address")("countryCode")
    i = i + 1
    Next
    MsgBox ("complete")
End Sub

Requsting your help to change the code such that i will get all the entries associated with the main key into different sub rows.

Improve this question
4
  • Take a look at this Commented May 6, 2020 at 12:40
  • You want to loop json("divisions") collection to get both levels. Bear in mind they both are nested. Commented May 6, 2020 at 16:23
  • @QHarr: I am still waiting for your help.. Commented May 18, 2020 at 10:19
  • Have you tried using a json viewer to inspect the structure and re-write your initial loop to be over json("divisions") Commented May 18, 2020 at 21:12
Related questions
6
Parsing a JSON object array in Excel VBA
0
Parsing JSON array in Excel VBA
1
Parsing Nested Arrays using VBA and JSON
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.