extract element from an array

Question

Hi I asked the similar question before: I have a large JSON file but the information I need is only small part of it. However, that part is a two dimensional array from "text"."size" part.Is any way I can loop each array and and get the number from what I have already?

{"widget": { "debug": "on", "window": { "title": "Sample Konfabulator Widget", "name": "main_window", "width": 500, "height": 500 }, "image": { "src": "Images/Sun.png", "name": "sun1", "hOffset": 250, "vOffset": 250, "alignment": "center" }, "text": { "data": "Click Here", "size": [[0,1,2,3,4],[5,6,7,8,9],[10,11,12,13,14]], "style": "bold", "name": "text1", "hOffset": 250, "vOffset": 100, "alignment": "center", "onMouseUp": "sun1.opacity = (sun1.opacity / 100) * 90;" } } }

I was taught from someone(thank you!) to use the following code

Option Explicit
Public Sub GetInfo()
    Dim strJSON As String, json As Object, rowNumber As Long
    Application.ScreenUpdating = False
    Const PATH As String = "C:\Users\User\Desktop\test.JSON"
    strJSON = GetJSONFromFile(PATH)
    Set json = JsonConverter.ParseJson(strJSON)
    Set json = json("widget")("text")
    Dim key As Variant
    With ThisWorkbook.Worksheets("Sheet1")
        For Each key In json
            rowNumber = rowNumber + 1
            .Cells(rowNumber, 1) = key
            .Cells(rowNumber, 2) = json(key)
        Next key
    End With
    Application.ScreenUpdating = True
End Sub

Many Thanks

Sort of a "Did you turn it off and on again" type question, but did you load in the jsonconverter module? — JNevill
– JNevill, Commented Sep 5, 2018 at 17:48

QHarr · Accepted Answer · 2018-09-06 07:26:53Z

1

Seeing expected output would help. If you are simply after the items within the collection of collections returned by JsonConverter.ParseJson(strJSON)("widget")("text")("size") then the following will empty them.

Option Explicit
Public Sub GetInfo()
    Dim strJSON As String, json As Object, rowNumber As Long, i As Long, j As Long
    Const PATH As String = "C:\Users\User\Desktop\test.JSON"
    strJSON = GetJSONFromFile(PATH)
    Set json = JsonConverter.ParseJson(strJSON)("widget")("text")("size") '<collection of collections

    With ThisWorkbook.Worksheets("Sheet1")
        For i = 1 To json.Count
            For j = 1 To json(i).Count
                rowNumber = rowNumber + 1
                .Cells(rowNumber, 1) = json(i)(j)
            Next
        Next
    End With
End Sub
Public Function GetJSONFromFile(ByVal PATH As String) As String
    Dim fso As Object, f As Object, outputString As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.OpenTextFile(PATH)

    Do Until f.AtEndOfStream
        outputString = f.ReadAll()
    Loop
    f.Close

    GetJSONFromFile = outputString
End Function

JSON structure:

You can see the path down to the collection of collections in the JSON structure below. The {} are dictionaries and the [] are collections.

edited Sep 6, 2018 at 7:26

answered Sep 6, 2018 at 7:20

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

3 Comments

Daniel Over a year ago

However, I do see a disadvantage for this out. It did not list each collection in a different columns.... working on it now but any help will be appreciated. Thanks

Daniel Over a year ago

Yes, sorry I thought you meant the flag. First time user.

QHarr Over a year ago

No worries :-) any questions let me know

Collectives™ on Stack Overflow

extract element from an array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related