Parse JSON with Excel VBA

Ferdi24 · Jun 14, 2012

Hi Experts,

I'm trying to parse a JSON string in Excel VBA into an array so that I can write it in table-format into a sheet.

Example: I have this JSON string in cell A1 "rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]

The final result in my sheet should look like this:

20120604 | ABC | 89
20120604 | BCD | 20
20120604 | CDE | 239

The format of the content inbetween the [ ] could be anything.

Example 1:
"rows":[["20120604","122"],["20120604","239"],["20120604","150"]]
Example2:
"rows":[["ABC","34"],["BCD","111"],["CDE","459"]]
Example3:
"rows":[["20120604", "ABC", "89"],["20120604", "BCD", "120"],["20120604", "CDE","239"]]
Example4:
"rows":[["XYZ", "ABC", "89","15"],["WXY", "BCD", "120","11"],["VWX", "CDE","239","19"]]

Does anyone have any ideas and would be willing to share his/her wisdom ith me?
Thanks a lot for reading

Ferdi24 · Apr 5, 2016

Well that depends...
JSON is an object-based format, Excel thinks in tables.
You cannot write "one size fits all" code that would convert any JSON into a table.

If you have an example what your JSON looks like and what your desired Output in Excel is, it's fairly easy to write VBA to do what you are asking for.

Catalin Bombea · Apr 27, 2016

My advice: use Power Query...
Save the JSON string in a text file, then create a power query to process the text file. Power Query has built in tools to process JSON.
Create a new query from text file, then in Source settings (the gear icon), open the file as JSON, see the attached example Query:

Code:

let
    Source = Json.Document(File.Contents("E:\Download\Toggl API\JSON.txt"),1252),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{5}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "pid", "tid", "uid", "description", "start", "end", "updated", "dur", "user", "use_stop", "client", "project", "project_color", "project_hex_color", "task", "billable", "is_billable", "cur", "tags"}, {"id", "pid", "tid", "uid", "description", "start", "end", "updated", "dur", "user", "use_stop", "client", "project", "project_color", "project_hex_color", "task", "billable", "is_billable", "cur", "tags"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"start", type datetimezone}, {"end", type datetimezone}}),
    #"Expanded tags" = Table.ExpandListColumn(#"Changed Type", "tags")
in
    #"Expanded tags"

The result will be a much cleaner table, and the same type of API request will be processed with a simple query refresh (most probably the API response has the same structure, for the same request)

Cheers,

Catalin Bombea Learn Excel - Excel First
Excel Support Guru at www.myonlinetraininghub.com

carlos_7 · Apr 30, 2016

hi, i am new, i am from spain and i am doing my final project about this( macro visual basic and json)
the problem that i have is that i have to take a json from a web that i have previously created and put the different objects in excel cells.
i know how to take the json from the website and i achieve to show it by msgbox in the macro but i don't know how to put the difference parts of the json in the cells of the excel.
thanks
i will be waiting for the answer
tell me if you need the excel or something like that
carlos

ahmeddavid · Sep 10, 2016

juan pablo hello , your code looks very good but in my case causes an error and does not allow me to create the collection object because parce function generates an object and not a collection , as can be corrected ? I really appreciate your help.

thierryt78 · Oct 18, 2016

Ferdi24 said:
Hi vbollepalli,
I have since switched to a different approach. Here's how it works for me now:

- Download and unzip the JsonConverter.bas file from https://github.com/VBA-tools/VBA-JSON
- In Excel open the VBA Editor (Alt+F11)
- Click 'File > Import file' and select the JsonConverter.bas
- Now go to 'Tools > References' and activate "Microsoft XML..." and "Microsoft Scripting Runtime"
That 's it really. Now you can write some code and use it like this:

Code:

Sub getGender() Dim httpRequest As New MSXML2.XMLHTTP60 Dim jsonText As String, firstName As String Dim jsonObject As Object firstName = Application.InputBox(Prompt:="What is your name?", Default:=Environ("username")) With httpRequest .Open "GET", "https://gender-api.com/get?name=" & firstName .send ("") jsonText = .responseText End With Set jsonobj = [URL="https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas"]JsonConverte[/URL]r.ParseJson(jsonText) MsgBox "I am " & jsonobj("accuracy") & "% sure that you, " & _ StrConv(jsonobj("name"), vbProperCase) & ", are " & jsonobj("gender") & "." End Sub

Hope that helps.

Hello,

I have problem to get values of the objects like jsonobl("name") in a cell in my excel sheet.
I'm trying to parse cells with json layout (they come from a kybana discover export) imported like csv.
And I want to transform them in excel cells layout to be able to filter.
Can you help to do this please ?

Thanks,

Thierry

Rasmita Nayak · Sep 5, 2019

Hi Experts,

Suppose i have a json string like:

jsonstr = [{"id":"BGFV:US","dateTimeRanges":{},"price":[{"date":"2019-08-05","value":2.07},{"date":"2019-08-06","value":2.02},{"date":"2019-08-07","value":2.03},{"date":"2019-08-08","value":1.98},{"date":"2019-08-09","value":1.98},{"date":"2019-08-12","value":1.9100000000000001},{"date":"2019-08-13","value":1.9100000000000001},{"date":"2019-08-14","value":1.8},{"date":"2019-08-15","value":1.7},{"date":"2019-08-16","value":1.77},{"date":"2019-08-19","value":1.8399999999999999},{"date":"2019-08-20","value":1.8199999999999998},{"date":"2019-08-21","value":1.9100000000000001},{"date":"2019-08-22","value":1.97},{"date":"2019-08-23","value":1.95},{"date":"2019-08-26","value":1.96},{"date":"2019-08-27","value":2.07},{"date":"2019-08-28","value":1.87},{"date":"2019-08-29","value":1.87},{"date":"2019-08-30","value":1.79},{"date":"2019-09-03","value":1.7},{"date":"2019-09-04","value":1.71}],"timeZoneOffset":-4,"nyTradeStartTime":"09:30:00.000","nyTradeEndTime":"16:30:00.000","priceMinDecimals":2,"lastUpdateDate":"2019-09-04","lastPrice":1.71}]

I am totally new in VBA and coding .I am trying to get data means " highest value" of the stock price of a month from the above json string.

So how i will get the highest value of the price from the above json string using excel vba.

Any ideas would be greatly appreciated!

Thanks in advance!

sagardemandsu · Dec 2, 2019

Hi Juan Pablo González

when i tried out your code i get 424 error , object required ..
my Json string :

Parse JSON with Excel VBA

Ferdi24

Board Regular

Ferdi24

Board Regular

Excel Facts

Catalin Bombea

New Member

carlos_7

New Member

ahmeddavid

New Member

thierryt78

New Member

Rasmita Nayak

New Member

sagardemandsu

New Member

Similar threads

Forum statistics

Share this page

Parse JSON with Excel VBA

Board Regular

Board Regular

Excel Facts

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock