Utilize ScriptControl for JSON parsing in VBA: convert output into dictionaries and collections

Looking to utilize Microsoft ScriptControl in VBA for parsing a JSON string and converting the resulting Object into Dictionary and Collection objects. While I have the parsing aspect down with ScriptControl, I'm struggling with how to translate the result into the Dictionary and Collection classes. Perhaps looping through the properties of an Object could provide some clarity...

Dim sc As ScriptControl
Dim obj As Variant

Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"

Set obj = sc.Eval("(" + json + ")") ' json is a string containing raw JSON

' What should be done next?

On another note, I have tried using the vba-json library for output in terms of Dictionaries and Collections, but it seems a bit sluggish compared to ScriptControl.

UPDATE: Came across a discussion on accessing object properties in this post.

jsonvba

Answer №1

By leveraging the power of JavaScript's JSON parsing capabilities with ScriptControl, we can develop a VBA parser that effectively extracts all data points from a JSON structure, no matter how intricate or nested it may be. As long as the input is valid JSON, our parser will generate a comprehensive tree representation of the data.

The functions provided by JavaScript such as Eval, getKeys, and getProperty serve as foundational tools for validating and accessing JSON content.

Implementing a recursive function in VBA allows us to recursively traverse through all keys within a JSON string, even at multiple levels deep. This information can then be organized using various methods including Tree control (as demonstrated in this post), dictionaries, or even on a simple Excel sheet.

VBA Code:

Answer №2

For iterating through objects in Excel VBA using parsed JSON data, refer to the link here. Implementing a myitem(n) method in JavaScript can assist in creating loops that interact with VB code effectively.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

The Python code utilizing the requests.get().json() function seems to be stuck in an

I am currently working on scraping data from a table located at import pandas as pd import requests import json headers = {'Host': 'stats.nba.com','User-Agent': 'Firefox/55.0','Accept': 'application/ ...

pythonjsonpython-requests

Comparing JSON in Clojure

During my testing, I have been comparing JSON results from various HTTP calls. However, I realized that simply comparing strings is too simplistic and unreliable, especially if the order of elements changes. Can you suggest a better way to compare two JSON ...

jsonclojureclojure-java-interop

Obtaining JSON Response from a Function in Wordpress using AJAX

Can anyone guide me on how to receive a function as a JSON response in my AJAX call after the document loads? Here is what I've attempted so far: Here is my HTML <!--Where I want to load my function--> <div id="applications"></div> ...

phpjqueryjsonajaxwordpress

Support needed to extract MQTT JSON data and convert it into individual entities using a

I am currently setting up my deebot robot to work with MQTT through Node-Red integration. I have successfully configured everything in Node-Red, but I am facing an issue with splitting values from the MQTT message. Here is a snippet of what I receive (mult ...

jsonmqtthome-assistant

Having trouble creating an angularjs table using ng-repeat in the controller?

I have a sample snippet that I would like to discuss. My goal is to display a JSON object in an angular table using ng-repeat, which is being generated from my angular controller script. I have attempted the code below, but for some reason, the table is no ...

javascriptangularjsjsonangularjs-ng-repeat

Understanding Json data using Jquery

I am currently learning about Jquery, Ajax, and JSON but I am having difficulty with parsing Json data. Despite researching extensively on stackoverflow Parsing JSON objects for HTML table Access / process (nested) objects, arrays or JSON Parse JSON in ...

javascriptjqueryajaxjsonparsing

Generating clickable markers that trigger an AlertDialog to appear in real-time

I am currently working on dynamically adding markers to a map, pulling data from JSON. The marker addition process is functioning correctly, as I can successfully add multiple markers in various locations. However, when I click on a marker, I want an alert ...

androidjsongoogle-mapsgoogle-maps-markersandroid-alertdialog

Parsing JSON data in Node.js using Express

I am attempting to extract the username from a JSON request. router.post('/', function (req, res) { console.log(req.body.username); }); Upon checking the console: undefined POST /question 200 39.384 ms - 28 When I modified it to this: rout ...

jsonnode.jsexpress

retrieve the JSON information from a secure website using HTTPS

Is there a way to retrieve JSON data from an external webpage and incorporate it into my Xpages? The external website uses the https protocol. I'm unable to use: var http_request = new XMLHttpRequest(); http_request.open("GET","XX"); If anyone has ...

jsonhttpsxpageslotus-noteslotus

Using JQuery Ajax to post an array

I have searched extensively for solutions, but none seem to fit my situation. Currently, I am utilizing the Jquery-Plugin DataTable with multiple tables within a form. The exact number of tables is unknown. The challenge lies in retrieving form data from ...

jqueryajaxjson

Leveraging OPENJSON in SQL Server Query

I'm facing a challenge with extracting information from a JSON array stored in an SQL Server database. While I understand that the OPENJSON function needs to be used for this task, most examples I've come across focus on declaring a single JSON a ...

sqljsonsql-serveropen-json

Retrieve information from a .json file using the fetch API

I have created an external JSON and I am trying to retrieve data from it. The GET request on the JSON is functioning correctly, as I have tested it using Postman. Here is my code: import "./Feedback.css"; import { useState, useEffect } from " ...

javascriptreactjsjsonreact-hooks

What are the reasons for the inability to send form-data in Postman?

Encountering an issue when trying to send form-data in postman as Sequelize returns an error: value cannot be null However, everything works fine when sending a raw request with JSON. Have tried using body-parser and multer, but no luck. This is my inde ...

jsontypescriptexpresspostmanmiddleware

a guide on transforming a string array into JSON

I initialized an array like this String[] finalcodes = new String[50] ; and populated it with some values. However, when I print finalcodes, the output is: ["aaa","bbb","ccc"] My goal is to convert this string array into a JSON Object. Can someone pl ...

javaarraysjsonstring

Validating two unique JSON responses using JSONSchema

Two JSON responses have been successfully validated against a defined schema using the jsonschema library. The responses have different field names but still pass validation. Below is the specified schema query_schema = { "type": "object", "prope ...

pythonjsonjsonschema

Retrieve the first two elements from an array and iterate through each of them using a foreach

Currently, I have JSON data formatted like this: https://i.stack.imgur.com/PqOQ0.png I am attempting to create URLs using a foreach loop for an API that requires start and finish dates as parameters. For example, here is a sample URL -> My question is, ...

phparraysjsonapiawr

Eliminate null values from a JSON dataset in Python

Whenever I fetch data from Firebase using the Rest API, the structure appears like this. { "Dataset1": [ null, { "Key1": 1, "Key2": 2 }, { "Key1": 3, "Key2": 4 ...

pythonjsonpython-3.xfirebase-realtime-database

Decoding a JSON array containing multiple JSON objects into an ArrayList of org.json.JSONObject instances using Gson

I have a JSON string that looks like this: { "r": [ { "pic": "1.jpg", "name": "Name1" }, { "pic": "2.jpg", "name": "Name2" }, { "pic": "3.jpg", "name": "Name3" } ] } My goal is to convert it ...

javaandroidjsonarraylistgson

Having trouble displaying values from nested JSON in a datatable

Response from server : ["{\"CLIENT\":[{\"tranche\":\"1-4\",\"prix\":\"65.96\",\"currency\":\"E\"}],\"DISTRIBUTEUR\":[{\"tranche\":\"1-4\",\"prix\ ...

javascriptjqueryangularjsjson

Mastering the Art of Defining JSON Information

I'm curious about how to obtain a single stream of data. Specifically, I want to retrieve the value for ModbusSerial.Port_1.COM.Power from the script. However, I'm unsure of how to write the code for that. I believe it is something similar to: js ...

pythonjson