VBA reading a JSON

Navigation
0

I'm trying to read a Json that returns a web service and show the data in an Excel using VBA. I call her with this: 

Dim hReq As Object    
Dim strUrl As String
strUrl = "http://api.worldweatheronline.com/premium/v1/weather.ashx?key=db3927718fdf4a45ad6110811182009&q=London&format=json&num_of_days=5"

Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", strUrl, False
        .send
    End With

If after this I put 

MsgBox hReq.responseText

shows me the Json without problems. The Json would be this (it is cut out since it is very large, but for my question this is enough): 

{
"data": {
    "request": [
        {
            "type": "City",
            "query": "London, United Kingdom"
        }
    ],
    "current_condition": [
        {
            "observation_time": "01:43 PM",
            "temp_C": "21",
            "temp_F": "70",
            "weatherCode": "116",
            "weatherIconUrl": [
                {
                    "value": "http://cdn.worldweatheronline.net/images/wsymbols01_png_64/wsymbol_0002_sunny_intervals.png"
                }
            ],
            "weatherDesc": [
                {
                    "value": "Partly cloudy"
                }
            ],
            "windspeedMiles": "21",
            "windspeedKmph": "33",
            "winddirDegree": "220",
            "winddir16Point": "SW",
            "precipMM": "0.1",
            "humidity": "60",
            "visibility": "10",
            "pressure": "1010",
            "cloudcover": "75",
            "FeelsLikeC": "21",
            "FeelsLikeF": "70"
        }
    ]
}
}

If in my VBA code I put below 

Dim JSON As Object
Set JSON = JsonConverter.ParseJson(hReq.responseText)
For Each item In JSON("data")
    MsgBox item
Next item

I'm showing windows with the values "request" and "current_condition". But what I would be interested in is, for example, writing in the cell (1,1) the value that appears in "value" that is inside "weatherDesc" and in turn inside "current_condition" (what I want to appear in the cell is "Partly cloudy"). I'm trying with the following code: 

Dim ws As Worksheet
Set ws = Worksheets("Hoja1")
ws.Cells(1, 1) = JSON("data")("current_condition")(0)("weatherDesc")(0)("value")

But I get an error: The error '9' occurred at runtime: subscript out of range

Can someone please tell me how I can write this information? Thanks.

    
asked by Ucha 21.09.2018 в 12:13
source

3 answers

0

You do not need For Each 

Option Explicit
Public Sub CurrentWeatherDescription()
    Dim hReq As Object, JSON As Object
    Dim ws As Worksheet: Set ws = ActiveSheet
    Const URL As String = "http://api.worldweatheronline.com/premium/v1/weather.ashx?key=db3927718fdf4a45ad6110811182009&q=London&format=json&num_of_days=5"

    Set hReq = CreateObject("MSXML2.XMLHTTP")
    With hReq
        .Open "GET", URL, False
        .send
    End With

    Set JSON = JsonConverter.ParseJson(hReq.responseText)
    ws.Cells(1, 1) = JSON("data")("current_condition")(1)("weatherDesc")(1)("value")
End Sub

Route:

    
answered by 25.09.2018 / 11:01
source
1

I made it! The correct code would be: 

Dim strUrl As String
Dim hReq As Object  
Dim JSON As Object
Dim salida As Object  
Dim ws As Worksheet: Set ws = ActiveSheet

strUrl = "http://api.worldweatheronline.com/premium/v1/weather.ashx?key=db3927718fdf4a45ad6110811182009&q=London&format=json&num_of_days=5"

Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
    .Open "GET", strUrl, False
    .send
End With

Set JSON = JsonConverter.ParseJson(hReq.responseText)

For Each salida In JSON("data")("current_condition")
    ws.Cells(1, 1) = salida("weatherDesc")(1)("value")
Next
    
answered by 21.09.2018 в 14:18
0

The error that is giving you is because JSON("data")("current_condition")(0)("weatherDesc")(0)("value") does not exist. These are parsing the contents of the JSON in an object so this will not have the linked properties of the JSON content, you should pair it to a typed class to be able to access the properties. I would say that the only thing that has your JSON object is a string with all the content of the json that you redibes from the Webservice.

I recommend using another technology instead of VBA, I have worked a lot programming Excel with C # with Closed and Open XML libraries for the kind of things you are doing. You can download Visual Studio Community for free and this program will allow you to have many more tools for the entire programming process. C # is much more used than VB by the developer community.

    
answered by 21.09.2018 в 14:19
Image from imageList to Picturebox comes out blurred (c #) Android listview arrayadapter sqlite