Json and loop through each line

[jaryszek]

Hello,

i have json files (a few) like here:

{
  "Test" : "VB_variable", "Test2" : true,
  "Test3": "http://google.com",
  "attributes":[
       {   "cell":"VB_answer", "property": "highAvailability" }
  ] 
}

All what i want to get list all values starting from VB*.

I am trying to build macro based on code from link below:

https://github.com/VBA-tools/VBA-JSON



Expected result here:
VB_variable
VB_answer

How can i achieve this using loop or some other code?

Best,
Jacek

 

[jaryszek]

anyone ?

Jacek

 

[John_w]

Try this (with JsonConverter from the link).

Public Sub Test()
    Dim JSONstring As String, ParsedJSON As Object
    JSONstring = "{ ""Test"" : ""VB_variable"", ""Test2"" : true, ""Test3"": ""http://google.com"", ""attributes"":[ { ""cell"":""VB_answer"", ""property"": ""highAvailability"" }  ]}"
    Set ParsedJSON = JsonConverter.ParseJson(JSONstring)
    With ThisWorkbook.Worksheets(1)
        .Cells.Clear
        JSONfindValues ParsedJSON, .Range("A1"), "VB_"
    End With
End Sub


Private Function JSONfindValues(JSONvar As Variant, destCell As Range, findValue As String) As Long

    Dim n As Long
    Dim key As Variant
    Dim i As Long
    
    n = 0
    
    If TypeName(JSONvar) = "Dictionary" Then
        For Each key In JSONvar.keys
            n = n + JSONfindValues(JSONvar.item(key), destCell.Offset(n, 0), findValue)
        Next
    ElseIf TypeName(JSONvar) = "Collection" Then
        For i = 1 To JSONvar.Count
            n = n + JSONfindValues(JSONvar(i), destCell.Offset(n, 0), findValue)
        Next
    Else
        If InStr(1, JSONvar, findValue, vbTextCompare) > 0 Then
            destCell.Offset(n, 0).Value = JSONvar
            n = n + 1
        End If
    End If

    JSONfindValues = n
    
End Function

 

[jaryszek]

wow excellent !

thank you!
working!

Another solution:

Set JsonTS = FSO.OpenTextFile(objFile, ForReading)


JsonText = JsonTS.ReadAll


For i = 1 To InStrRev(JsonText, "Vn_")
    tempvar = InStr(i, JsonText, "Vn_")
    result1 = InStr(tempvar, JsonText, """")
    
    longer = result1 - tempvar
    
    resultAll = Mid(JsonText, tempvar, longer)
    
    i = result1 + 1
    
    counter = counter + 1
    
    Sheets(1).Cells(counter, 1).Value = resultAll
    
Next i


JsonTS.Close

Best,
Jacek