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-09 20:10:24Z

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']")

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, CommentedMar 9, 2021 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, CommentedMar 9, 2021 at 20:34

Wizhi · Accepted Answer · 2021-03-09 20:26:56Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Linked

Related