VBA parsing JSON file

[BarryCambridge]

I've been struggling to get the VBA-JSON parser to work for me and while I can see others have had the same problem, I can't find how it was fixed.

Sorry - I don't have sufficient posts to put links in my posts, but searching VBA-JSON will take you to GitHub and the source code for the parser module

It seems to be down to the structure of my JSON file which comes from a Gravity Forms Module in WordPress. I can import the file ok and pass it to the parser, but when I set a Watch to see what ends up in "coll", I see Item 1 "status, Item 2 "response" and then it seems to ignore the rest of the file containing the variables I actually want to add to my table! I think it's down to the positioning of the { and [ brackets.

The start of the JSON file I get is :

{"status":200,"response":{"total_count":"13","entries":[{"id":"699","form_id":"3","post_id":null,"date_created":"2018-09-01 06:33:23","date_updated":null,"is_starred":"0","is_read":"0","ip":"xxxxxx","source_url":"https:\/\removed for post\/register-your-interest ………………..

The tutorial I have used says the parser will return a Dictionary or Collection depending on what it finds. If I delclare "coll" as a dictionary, it works as above. If I declare it as a Collection, I get a Type Miss Match Error.

As you can probably tell, I'm no expert, so I hope this makes some sort of sense and any pointers would be much appreciated. It's a bit frustrating to have it so nearly working!
Many thanks, Barry

 Public Function ParseWordpressData()

Dim httpReq As New MSXML2.ServerXMLHTTP
Dim coll As Dictionary

Dim db As DAO.Database
Dim rs As DAO.Recordset

    httpReq.Open "GET", "etc, etc
    
    httpReq.send
  
    Debug.Print httpReq.responseText  
           
 If httpReq.status = 200 Then
 
 Set coll = JsonConverter.ParseJson(httpReq.responseText)
 
 'Debug.Print JsonConverter.ConvertToJson(coll, Whitespace:=2)
    
      Set db = CurrentDb
     Set rs = db.OpenRecordset("tbl_wordpress_api", dbOpenDynaset, dbSeeChanges)

 

[pbaldy]

I've done very little but Colin has a sample db here:

https://access-programmers.co.uk/forums/showthread.php?t=295789

 

[jleach]

This might help also: it's bare-bones minimum: https://dymeng.com/parsing-json-with-vba/

hth

 

[isladogs]

The example database of mine that Paul referred to is a very early version of what eventually became a commercial application available from my website.

It also makes some limited use of the GitHub code by Tim Hall but I soon found that code had major limitations.

As you have already found out it fails on some perfectly valid JSON files. It also can't handle very large files above 10 Mb or so.

I can see exactly why the GitHub code isn't reading anything after response: and it really is as simple as a missing [

Due to my own frustration with the GitHub code, I wrote my own parsing and analysis code from scratch to overcome these limitations. If you want to know more, send me a PM or email me. Alternatively go to my website and click the link for JSON Analyse and Transform for Access (JATFA)

 

[BarryCambridge]

Thanks all, that's very helpful. I'll have a look through and see if I can sort it out. In many ways, it's nice to hear the code has limitations and issues, as I can now move on from it.

 

[isladogs]

On behalf of all of us, you're welcome.
If you need more help later, you know what to do....

 

[arnelgp]

if you use Jacks codr and you are using x64 access, it will not work.
ive modified the code to make it work.