Unable to parse the name from json response

[shahin]

This time I tried to parse data from json response using string manipulation. Upon running my script. I get nothing as result. Apparently, it seems to me that I did it the right way. However, a little twitch on my code might lead me to the right direction. Thanks in advance. Here is the link to that :"http://jsonplaceholder.typicode.com/users" and this is what I tried:

Sub Json_data()
Dim http As New XMLHTTP60, itm As Variant
    With http
        .Open "GET", "http://jsonplaceholder.typicode.com/users", False
        .send
        itm = Split(.responseText, "id"":")
    End With
    x = UBound(itm)
    On Error Resume Next
    For y = 1 To x
        Cells(y, 1) = Split(Split(str(y), "name"":"" """)(1), """")(0)
        Cells(y, 2) = Split(Split(str(y), "username"":"" """)(1), """")(0)
    Next y
End Sub

 

[NARAYANK991]

Hi ,

Try this :

Sub Json_data()
    Dim http As New XMLHTTP60, itm As Variant
   
    With http
        .Open "GET", "http://jsonplaceholder.typicode.com/users", False
        .send
        itm = Split(.responseText, "id"":")
    End With
   
    x = UBound(itm)
   
    On Error Resume Next
   
    For y = 1 To x
        Cells(y, 1) = Split(Split(itm(y), " " & y & "," & Chr(10) & "    ""name"": """)(1), """")(0)
        Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0)
    Next y
End Sub

Narayan

 

[shahin]

Thanks a lot Narayan, for your robust, flawless and effective answer. It is very soothing that I always found you quick and precise. I tried with my code several times with many different way before your intervention but it could not bring any result cause I copied and pasted the gist portion of my code to save time and mistakenly kept that "str" unchanged. Thanks once again.

 

[shahin]

Here is the complete code to parse data from the link:

Sub Json_coder()
Dim http As New XMLHTTP60, itm As Variant
    With http
        .Open "GET", "http://jsonplaceholder.typicode.com/users", False
        .send
        itm = Split(.responseText, "id"":")
    End With
    x = UBound(itm)

    For y = 1 To x
        Cells(y, 1) = Split(Split(itm(y), "name"": """)(1), """")(0)
        Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0)
        Cells(y, 3) = Split(Split(itm(y), "email"": """)(1), """")(0)
        Cells(y, 4) = Split(Split(itm(y), "street"": """)(1), """")(0)
        Cells(y, 5) = Split(Split(itm(y), "suite"": """)(1), """")(0)
        Cells(y, 6) = Split(Split(itm(y), "city"": """)(1), """")(0)
        Cells(y, 7) = Split(Split(itm(y), "zipcode"": """)(1), """")(0)
        Cells(y, 8) = Split(Split(itm(y), "phone"": """)(1), """")(0)
        Cells(y, 9) = Split(Split(itm(y), "website"": """)(1), """")(0)
        Cells(y, 10) = Split(Split(Split(itm(y), "company"": ")(1), "name"": """)(1), """")(0)
        Cells(y, 11) = Split(Split(itm(y), "catchPhrase"": """)(1), """")(0)
        Cells(y, 12) = Split(Split(itm(y), "bs"": """)(1), """")(0)
    Next y
End Sub

Thanks again, Narayan.

 

[Marc L]

As in your previous post on exactly same subject
it may be easier with a JSon object than with string operations …
​

 

[shahin]

@Marc L,
Right you are. I wanted to be more comfortable with this json stuff. That's it. Thanks for your response, by the way.