The following describes using JSONConverter to set the JSON object and parse it for the required information.
After adding in the class, you must also add a reference to Microsoft Scripting Runtime via VBE > Tools > References.
If we take a moment to inspect the JSON we will see the following structure:
The left is the actual response and the right is the tree view.
The initial object returned is a dictionary with one key "result".
Using JSONConverter we can directly grab this dictionary value by its key which returns a collection of dictionaries. The "[" on the left hand side indicates the collection as with [7689] on the right. There are 3 items per dictionary and therefore 7689/3 = 2563 dictionaries. You could confirm this using the .Count property of the collection.
Set json = JsonConverter.ParseJson(strJSON)("result")
We can loop over the collection setting each item to a dictionary object variable/ or just Object for late binding.
For Each i In json
Set dict = i
'Other code
Next i
We can then loop over the individual dictionary keys:
For Each key2 In dict.Keys
'Other code
Next key2
Now, you have stated you are interested in values in the dictionaries with keys of "name", "type" and "latlong"; and that you want these to go into columns A, B and C respectively, starting from row 4.
So, we looping each dictionary we can use a Select Case statement on the dictionary key to determine which column the value should be written to:
Select Case key2
Case "name"
.Cells(counter, 1) = dict(key2)
Case "type"
.Cells(counter, 2) = dict(key2)
Case "latlong"
.Cells(counter, 3) = dict(key2)
End Select
After each dictionary is looped we need to add one to the counter variable which determines which row we write to, so we write to the next row. counter is started at 4 so we start writing from row 4 onwards.
Example output in sheet:
VBA Code:
Option Explicit
Public Sub GetInfo()
Dim strURL As String, strJSON As String
Dim http As Object
Dim json As Object, item As Long
Application.ScreenUpdating = False
strURL = "http://www.vehicletrack.biz/api/xlsmaster?usr=rahul&pwd=togodeamo&type=2"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.send
strJSON = http.responseText
Set json = JsonConverter.ParseJson(strJSON)("result") 'dictionary one key of "Result" which is a collection of dictionaries when accessed
Dim i As Object, dict As Object, key2 As Variant, counter As Long
counter = 4
For Each i In json
Set dict = i
For Each key2 In dict.Keys
With ActiveSheet
Select Case key2
Case "name"
.Cells(counter, 1) = dict(key2)
Case "type"
.Cells(counter, 2) = dict(key2)
Case "latlong"
.Cells(counter, 3) = dict(key2)
End Select
End With
Next key2
counter = counter + 1
Next i
Application.ScreenUpdating = True
End Sub
