Print value from JSON object to excel sheet (Parse JSON to VBA)

Question

I have for a long time used QHarr's excellent answer to parse JSON by using a JSON parser. Code used is: Set Json = JsonConverter.ParseJson(.responseText)

It have stopped working and it doesn't work for the following URL request URL = "https://api.gleif.org/api/v1/lei-records?filter[lei]=5493001ZDI5KQPZTPI85", so I have tried to use another method.

I'm trying to retrieve the legal name as an exampel:

It seems like I get a JSON object in return but I'm struggle with how I can print the values to the excel sheet. I thought this method would do the trick as suggested by July Tech:

ws.Cells(1, 1).Value = json("data")(0)("attributes")("legalName")("name")  'Entity Status (Active/Not Active)

But I only get error 438:

Full Code:

Sub getData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2") 'Set the outputdata sheet

Dim json As Object
Dim item As Object
Dim URL As String
Dim scriptControl As Object

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

URL = "https://api.gleif.org/api/v1/lei-records?filter[lei]=5493001ZDI5KQPZTPI85"

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", URL, False
    .send
    Set json = scriptControl.Eval("(" + .responseText + ")")
    'MsgBox scriptControl.Eval("(" + .responseText + ").data.attributes.lei")
    .abort
End With



Dim jsent As Variant
ws.Cells(1, 1).Value = json("data")(0)("attributes")("legalName")("name")  'Entity LegalName

End Sub

Tim Williams · Accepted Answer · 2021-03-09T20:10:24.467

2

You are missing "entity" from your path:

Dim json As Object

Set json = ParseJson(Range("a1").Value) 'loading json from a cell for testing...

Debug.Print json("data")(1)("attributes")("entity")("legalName")("name") 'BREVAN HOWARD MASTER FUND LIMITED

FYI it's super-unsafe to use the Scriptcontrol to parse JSON from any site you don't control. That script control instance isn't running in a browser sandbox, so any js you execute inside it could spin up (eg) a new Scripting.Filesystemobject and start deleting your files.

For completeness though:

Dim scriptControl, json

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

Debug.Print scriptControl.Eval("(" + Range("A1").Value + ")['data'][0]['attributes']['entity']['legalName']['name']")

edited Mar 09 '21 at 20:10

answered Mar 09 '21 at 19:55

Tim Williams

154,628
8
97
125

Thanks!! Getting blind on this. I wanted to avoid it too, but started to get desperate. I have issues with using ParsJson but got it to work somehow now with the JSON parser! Thanks for taking your time. Much appreciated. – Wizhi Mar 09 '21 at 20:08
Thanks for the extra completeness. Always interesting to see the answer and learn more/new things. Gives a a better understanding. And a + plus for highlighting the drawback :) – Wizhi Mar 09 '21 at 20:34

Wizhi · Answer 2 · 2021-03-09T20:26:56.967

Working code, thanks to Tim Williams:

Sub getData()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2") 'Set the outputdata sheet

Dim json As Object
Dim item As Object
Dim URL As String
Dim scriptControl As Object

Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
scriptControl.Language = "JScript"

URL = "https://api.gleif.org/api/v1/lei-records?filter[lei]=5493001ZDI5KQPZTPI85"

Dim rslt As String

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", URL, False
    .send
    rslt = .responseText
    Debug.Print rslt
End With

Set json = ParseJson(rslt) 'loading json from a cell for testing...

Debug.Print json("data")(1)("attributes")("entity")("legalName")("name")  'Entity Status (Active/Not Active)
End Sub

Print value from JSON object to excel sheet (Parse JSON to VBA)

2 Answers2