how to pull elements from a list in vba

Question

I have extract something like this from a database:

[{"identifier":{"strategyType":"element1"},"elnSchedules":[{"paymentDate":["element2","element2"]},{"paymentDate":["element2","element2"]}],"composition":{"components":[{"instrument":{"exerciseType":["element3","element3"]}},{"instrument":{"exerciseType":["element3","element3"]}}]},"links":[]}]

I want to build a vba and create a table in excel that has header: strategyType, paymentDate, exerciseType

and elements: element1, element2, element3 under the corresponding header(while each element can only appear once).

so far I have: For i = 1 To jsonO.Count 'set headings If i = 1 Then j = 1 For Each StrKey In jsonO(i).Keys() activeWS.Cells(i + offset, j) = StrKey j = j + 1 Next End If

        j = 1
        For Each StrKey In jsonO(i).Keys()
            If (StrKey <> "links") Then
                activeWS.Cells(i + offset + 1, j) = jsonO(i)(StrKey)
                j = j + 1
            End If
        Next

But this only extracts identifier, eLnschedules, and composition and not able to get into specific element.

Is there any way to do it?

Thanks.

-------------------NEW QUESTION----------------------

I used what's posted on the answer and was trying to build a new function under the function posted in the answer(while both of them are called by the main function):

Public Sub GetEndDate()

Dim activeWS As Worksheet
Set activeWS = ThisWorkbook.Worksheets("Data")
Dim jsonStr As String, Json As Object, headers()
'headers = Array("strategyType", "paymentDate", "exerciseType")
jsonStr = [{"optionFeatures":{"Strike Setting":[{"endDate":["2018-10-16"]}]},"links":[]}] '<== read from cell
Set Json = JsonConverter.ParseJson(jsonStr)(1)
activeWS.Cells(1, 13) = Json("optionFeatures")("Strike Setting")("endDate")

End Sub

However it was not able to read from the string, or do I need to reset the lib again?

Thanks.

code = [{"identifier":{"strategyType":"element1"},"elnSchedules":[{"paymentDate":["element2","element2"]},{"paymentDate":["element2","element2"]}],"composition":{"components":[{"instrument":{"exerciseType":["element3","element3"]}},{"instrument":{"exerciseType":["element3","element3"]}}]},"links":[]}] — user10382480, Oct 22 '18 at 16:40
what do you mean by each element can only appear once? Does that appear you only want the first if it appears twice? — QHarr, Oct 22 '18 at 16:42
A list is not a VBA data type. Is this a string? Of course you can write a VBA program which will take such strings and use them to populate a table, although any such program will involve nontrivial parsing. What issued have you encountered in your attempts to write such a program? Please show some relevant code. — John Coleman, Oct 22 '18 at 16:42
yep (as you can see there are several element2, while I only want the first one as they are all the same — user10382480, Oct 22 '18 at 16:43
This is a JSON string but I think OP needs to examine the structure and clarify what they want from it. — QHarr, Oct 22 '18 at 16:43
yep it is a string, i just don't know how to grab those element from the string — user10382480, Oct 22 '18 at 16:44
elnSchedules, for example, is a collection with 2 items which are identical dictionaries containing a collection called instrument which repeats element 3 4 times. 2 in each collection. — QHarr, Oct 22 '18 at 16:45
you would use a JSON parser like JSONConverter.bas or Split function as you only want first occurences by the sounds of it. — QHarr, Oct 22 '18 at 16:46
This question might help: https://stackoverflow.com/q/6627652/4996248 — John Coleman, Oct 22 '18 at 16:46

score 1 · Accepted Answer · answered Oct 22 '18 at 16:57

1

Using JSONConverter.bas to parse the JSON string read in from a cell as shown below. This assumes you only want one instance of each value.

Note:

After adding in JSONConverter.bas you need to go VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

Your JSON structure is as follows:

[] indicates a collection, items accessed by index starting from 1. {} indicates a dictionary with items accessed by key.

I traverse the tree using the appropriate syntax to retrieve the first occurrence of each element.

Option Explicit
Public Sub GetInfoFromSheet()
    Dim jsonStr As String, Json As Object, headers()
    headers = Array("strategyType", "paymentDate", "exerciseType")
    jsonStr = [A1] '<== read from cell
    Set Json = JsonConverter.ParseJson(jsonStr)(1)

    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(1, 1).Resize(1, UBound(headers) + 1) = headers
        .Cells(2, 1) = Json("identifier")("strategyType")
        .Cells(2, 2) = Json("elnSchedules")(1)("paymentDate")(1)
        .Cells(2, 3) = Json("composition")("components")(1)("instrument")("exerciseType")(1)
    End With
End Sub

answered Oct 22 '18 at 16:57

QHarr

83,427
12
54
101

What do you mean by add a reference to Microsoft Scripting Runtime.? I tried to run it but it shows JsonConverter not defined. Thanks. – user10382480 Oct 22 '18 at 17:42
Did you download the .bas and add it to your project? – QHarr Oct 22 '18 at 17:52
The converter works now, thanks. But what if some of the elements are missing/empty? As the code would not skip any empty elements... – user10382480 Oct 23 '18 at 17:28
You asked how to drill down. I am showing you how to think about it. Otherwise, you would need to loop testing with TypeName whether you are dealing with Dictionary or Collection. – QHarr Oct 23 '18 at 18:05
Are some of the elements empty? See my answer here for looping https://stackoverflow.com/a/49011943/6241235 – QHarr Oct 23 '18 at 18:59

how to pull elements from a list in vba

1 Answers1