I have a json like with this format
[{"ts_id": "96643010","rows": "64","columns":"Timestamp,Value", "data": [["2021-09-01T11:00:00.000+07:00",206.95],["2021-09-01T12:00:00.000+07:00",206.96],["2021-09-01T13:00:00.000+07:00",206.97],["2021-09-01T14:00:00.000+07:00",206.95],["2021-09-01T15:00:00.000+07:00",206.92],["2021-09-01T16:00:00.000+07:00",206.89],["2021-09-01T17:00:00.000+07:00",206.86],["2021-09-01T18:00:00.000+07:00",206.83],["2021-09-01T19:00:00.000+07:00",206.80],["2021-09-01T20:00:00.000+07:00",206.77],["2021-09-01T21:00:00.000+07:00",206.74],["2021-09-01T22:00:00.000+07:00",206.71],["2021-09-01T23:00:00.000+07:00",206.68],["2021-09-02T00:00:00.000+07:00",206.65],["2021-09-02T01:00:00.000+07:00",206.63],["2021-09-02T02:00:00.000+07:00",206.65],["2021-09-02T03:00:00.000+07:00",206.67],["2021-09-02T04:00:00.000+07:00",206.69],["2021-09-02T05:00:00.000+07:00",206.71],["2021-09-02T06:00:00.000+07:00",206.73],["2021-09-02T07:00:00.000+07:00",206.75],["2021-09-02T08:00:00.000+07:00",206.76],["2021-09-02T09:00:00.000+07:00",206.77],["2021-09-02T10:00:00.000+07:00",206.78],["2021-09-02T11:00:00.000+07:00",206.79],["2021-09-02T12:00:00.000+07:00",206.80],["2021-09-02T13:00:00.000+07:00",206.81],["2021-09-02T14:00:00.000+07:00",206.82],["2021-09-02T15:00:00.000+07:00",206.83],["2021-09-02T16:00:00.000+07:00",206.83],["2021-09-02T17:00:00.000+07:00",206.83],["2021-09-02T18:00:00.000+07:00",206.83],["2021-09-02T19:00:00.000+07:00",206.83],["2021-09-02T20:00:00.000+07:00",206.83],["2021-09-02T21:00:00.000+07:00",206.84],["2021-09-02T22:00:00.000+07:00",206.85],["2021-09-02T23:00:00.000+07:00",206.86],["2021-09-03T00:00:00.000+07:00",206.87],["2021-09-03T01:00:00.000+07:00",206.89],["2021-09-03T02:00:00.000+07:00",206.91],["2021-09-03T03:00:00.000+07:00",206.93],["2021-09-03T04:00:00.000+07:00",206.95],["2021-09-03T05:00:00.000+07:00",206.96],["2021-09-03T06:00:00.000+07:00",206.97],["2021-09-03T07:00:00.000+07:00",206.98],["2021-09-03T08:00:00.000+07:00",207.00],["2021-09-03T09:00:00.000+07:00",207.02],["2021-09-03T10:00:00.000+07:00",207.04],["2021-09-03T11:00:00.000+07:00",207.06],["2021-09-03T12:00:00.000+07:00",207.08],["2021-09-03T13:00:00.000+07:00",207.11],["2021-09-03T14:00:00.000+07:00",207.14],["2021-09-03T15:00:00.000+07:00",207.17],["2021-09-03T16:00:00.000+07:00",207.18],["2021-09-03T17:00:00.000+07:00",207.19],["2021-09-03T18:00:00.000+07:00",207.20],["2021-09-03T19:00:00.000+07:00",207.20],["2021-09-03T20:00:00.000+07:00",207.20],["2021-09-03T21:00:00.000+07:00",207.20],["2021-09-03T22:00:00.000+07:00",207.19],["2021-09-03T23:00:00.000+07:00",207.18],["2021-09-04T00:00:00.000+07:00",207.17],["2021-09-04T01:00:00.000+07:00",207.18],["2021-09-04T07:00:00.000+07:00",207.30]]}] Blockquote
I have tried to get this data with VBA but unsuccess, somebody can help me with VBA code to get data from "data". This the code i have used:
Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://cdh.vnmha.gov.vn/KiWIS/KiWIS?service=kisters&type=queryServices&request=getTimeseriesValues&datasource=0&format=dajson&ts_id=96643010&period=P3D", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("data")
i = i + 1
Next
MsgBox ("complete")
End Sub