JSON Class

[JimChristmas]

Hello everyone.

I've been searching around for a VBA class that neatly handles JSON data. I'm wondering if anybody here has a class module that they've been using for a while and are entirely satisfied with.

My searches thus far have turned up some code that appears to handle parsing very well, but doesn't provide for adding elements to an existing JSON object, or allow you to create a new JSON object from scratch. I was hoping to have something with relatively little bloat because the db is already massive with almost 100 code modules including nearly thirty classes.

It'd be wonderful if I don't have to reinvent the wheel, and could lean on the generosity of someone who's already traveled this road.

Thanks in advance!

Jim C.

 

[Josef P.]

Perhaps this is what you need:

GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

JSON conversion and parsing for VBA. Contribute to VBA-tools/VBA-JSON development by creating an account on GitHub.

github.com

You can append data to the collection and dictionary objects and then generate a JSON string from them again.

 

[GPGeorge]

That's the one I use extensively.

 

[Josef P.]

Josef,
Thanks for your reply. Are you using that module yourself?
It's one of the ones I had found when looking earlier. I didn't look at it too hard because it won't compile for me. It complains about the line:
Public JsonOptions As json_Options
I merely imported it into my project and saved it as a new class.
Have I done something incorrectly?

Sending you a direct message because the system seems to think that the text above is some sort of spam. :-/

Download JsonConverter.bas and import with VBE (Menu: File - Import file)
Insert reference to Microsoft Scripting Runtime (Call from Immediate window: Application.References.AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0)

Now test it:

Private Sub TestJson()

   ' Object = Dictionary  ... Json: {...}
   ' Collection = Array, List, .. Json: [...]

   Dim JsonString As String
   JsonString = Replace("[ {'Key': 'abc', 'Value': 123}, {'Key': 'xyz', 'Value': 456} ]", "'", """")

   Dim JsonObject As Object
   Set JsonObject = JsonConverter.ParseJson(JsonString)
   Stop ' see structure in locals window

   'Add new Object to Collection
   Dim NewObject As Dictionary
   Set NewObject = New Dictionary
   NewObject.Add "Key", "zyx"
   NewObject.Add "Value", 135
   JsonwObject.Add NewObject
   Stop ' see structure in locals window

   Debug.Print JsonConverter.ConvertToJson(JsonObject)

End Sub

 

[theDBguy]

Hello everyone.

I've been searching around for a VBA class that neatly handles JSON data. I'm wondering if anybody here has a class module that they've been using for a while and are entirely satisfied with.

My searches thus far have turned up some code that appears to handle parsing very well, but doesn't provide for adding elements to an existing JSON object, or allow you to create a new JSON object from scratch. I was hoping to have something with relatively little bloat because the db is already massive with almost 100 code modules including nearly thirty classes.

It'd be wonderful if I don't have to reinvent the wheel, and could lean on the generosity of someone who's already traveled this road.

Thanks in advance!

Jim C.

Hi. Welcome to AWF!

Not free, but maybe you can also consider this one.

JSON Analyse & Transform

JSON Analyse & Transform for Access makes importing and handling JSON files a straightforward and largely automated task.

isladogs.co.uk

 

[Josef P.]

Message from JimChristmas:

Josef and GPGeorge,
Gentlemen, thank you! My mistake was not realizing that code modules were made to be imported like that. I guess I'm still living in the stone age because whenever I go to import code, I just highlight and Control-C it on over.
This looks like it'll work wonderfully.
Danke!

Again, I'm having to respond here because the site seems to think everything I type is spam. I'm starting to feel like quite the troglodyte. Is it because I'm a provisional user, or because I posted this topic under the wrong category? Apologies for the extra work I've caused.

 

[DickyP]

Depending what you want to do with JSON and you are happy to use a different BE (which has about the same power and limitations as Acceses) SQLite has a full suite of JSON extensions to its SQL.

SQLITE SQL