search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.76k stars 568 forks source link

Convert JSON to #181

Closed rdwick81 closed 3 years ago

rdwick81 commented 3 years ago

I'm new to JSON and even newer to VBA dictionaries so I need a little help. I'm trying to read through the full list of keys, their values and the arrays. My end goal is to convert the JSON to an Excel spreadsheet. I've made a For Each loop that displays each of the keys but that's as much as I've been able to figure out. Some of the JSON content I'll be working with gets pretty complex, as in multiple levels and arrays. Does anyone have a sample that would do what I'm looking for?

Where do I go from here? ResponseText contains the JSON returned from the API. Dim JsonParsed As Object Set JsonParsed = JsonConverter.ParseJson(objRequest.ResponseText)

Next I plan to take the changes made to the Excel doc, convert it back to JSON and post it back to the API, but I'll save that for later.

aholden10 commented 3 years ago

I think a lot of people have been down the same road. I needed to do this a few months ago and ended up using JSON2Array discovered from the links below: https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop https://github.com/shafiq2601/json2Array-VB

It worked well, but it is slow - that's not a criticism of the program, the Excel environment is just not the fastest at churning through a lot of data.  So I ended up taking the plunge and converting the JSON piece to Python and what took 4 or 5 hours in Excel now takes a few minutes in Excel and Python. Anyway, hope this helps!

On Monday, November 23, 2020, 06:23:16 PM EST, rdwick81 <notifications@github.com> wrote:

I'm new to JSON and even newer to VBA dictionaries so I need a little help. I'm trying to read through the full list of keys, their values and the arrays. My end goal is to convert the JSON to an Excel spreadsheet. I've made a For Each loop that displays each of the keys but that's as much as I've been able to figure out. Some of the JSON content I'll be working with gets pretty complex, as in multiple levels and arrays. Does anyone have a sample that would do what I'm looking for?

Where do I go from here? ResponseText contains the JSON returned from the API. Dim JsonParsed As Object Set JsonParsed = JsonConverter.ParseJson(objRequest.ResponseText)

Next I plan to take the changes made to the Excel doc, convert it back to JSON and post it back to the API, but I'll save that for later.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.