CODEWITHSUNDEEP
jsonvbaweb-scraping
SMTH
SMTH

Reputation: 95

Can't parse a certain field which is within JSON from a webpage using VBA

I'm trying to parse property information from this link which produces a JSON response. I've used here JSON and VBA converter. However, when I run the script below, I get an error keyNotFoundError. I'm trying to parse the value of properties which is within features.

Public Sub parseJson()
    Dim jsonObject As Object, oElem As Variant
    Dim resp$, Url$, R&

    Url = "https://torontolife.com/wp-content/themes/sjm-underscores/inc/neighbourhoods/2015/compiled.json"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.182 Safari/537.36"
        .send
        resp = .responseText
    End With
    
    Set jsonObject = JsonConverter.parseJson(resp)

    For Each oElem In jsonObject("features")
        Debug.Print oElem("properties")
    Next oElem
End Sub

I also get the same error when I try to like the following:

Sub Demo()
    Dim Json As Object
    JsonString = "[{""Entries"":[{""Name"": ""SMTH"",""Gender"": ""Male""}]}]"
    JsonConverter.JsonOptions.AllowUnquotedKeys = True
    Set Json = JsonConverter.ParseJson(JsonString)

    Debug.Print Json(1)("Entries")
End Sub

I'm on Windows 7 (32 bit) and I'm using this library.

One more thing, they are valid JSON and I didn't encounter any error while parsing the same using Python.

Upvotes: 1

Views: 326

Answers (2)

DecimalTurn
DecimalTurn

Reputation: 4129

I saw a GitHub issue about this and some googling led me here. Here's a copy of my comment on GitHhub:

I've found the source of the KeyNotFoundError. It is caused by the presence of the Selenium Type Library in your VBA project. Selenium contains a Dictionary object and the prioritization of the references can be such that VBA will use the Selenium version of the Dictionary object instead of the one from Microsoft Scripting Runtime.

Here's a screenshot from the Object Browser (F2): image

To solve this issue, make sure that Selenium Type Library reference is sitting below Microsoft Scripting Runtime by reducing its priority:

image

Upvotes: 0

QHarr
QHarr

Reputation: 84465

Your code should be failing because oElem("properties") is a dictionary. Furthermore, within that dictionary there are a mixture of datatypes associated with the keys so you will need to test the type and handle appropriately. Or use one of the many readily available programs which will handle that and empty the entire json object for you.

Option Explicit

Public Sub ParseJson()
    Dim jsonObject As Object, oElem As Variant
    Dim resp$, Url$, R&

    Url = "https://torontolife.com/wp-content/themes/sjm-underscores/inc/neighbourhoods/2015/compiled.json"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.182 Safari/537.36"
        .send
        resp = .responseText
    End With
    
    Set jsonObject = JsonConverter.ParseJson(resp)
    
    Dim key As Variant, propertyTypes As Scripting.Dictionary
    
    Set propertyTypes = New Scripting.Dictionary
    
    For Each oElem In jsonObject("features")
    
        For Each key In oElem("properties")
           Debug.Print key, vbTab, TypeName(oElem("properties")(key))
           propertyTypes(key) = TypeName(oElem("properties")(key))
        Next

    Next oElem
    'Review propertyTypes dict and/or immediate window print out
    Stop
End Sub

Upvotes: 1

Related Questions