json to excel VBA??

[Lo031514]

Hi everyone,

May I know if there is a solution to convert my json file to excel format...

below data extracts from json...
I would like to show
1sr row:add_amount,available_amount,budget_account_code,budget_account_name...
2nd row:0,0,R13006,A123...
3rd row:0,0R13006,A1234..

{"lastPage":false,"pageSize":10000,"pageNumber":1,"firstPage":true,"list":[
{"add_amount":0,"available_amount":0,"budget_account_code":"R13006","budget_account_name":"A123","budget_group_code":"R13","budget_group_name":"Other Operating Revenue","budget_no":"CT5000_R13006008","budget_year":2018,"id":48831,"month10_amount":0,"month11_amount":0,"month12_amount":0,"month1_amount":0,"month2_amount":0,"month3_amount":0,"month4_amount":0,"month5_amount":0,"month6_amount":0,"month7_amount":0,"month8_amount":0,"month9_amount":0,"month_total":0,"occupied_amount":0,"operating_activity_code":"R13006008","operating_activity_name":"B123","release_amount":0,"total_amount":0},
{"add_amount":0,"available_amount":0,"budget_account_code":"R13006","budget_account_name":"A1234","budget_group_code":"R13","budget_group_name":"Other Operating Revenue","budget_no":"CT5000_R13006009","budget_year":2018,"id":48832,"month10_amount":0,"month11_amount":0,"month12_amount":0,"month1_amount":0,"month2_amount":0,"month3_amount":0,"month4_amount":0,"month5_amount":0,"month6_amount":0,"month7_amount":0,"month8_amount":0,"month9_amount":0,"month_total":0,"occupied_amount":0,"operating_activity_code":"R13006009","operating_activity_name":"B1234","release_amount":0,"total_amount":0}
,......
],"totalRow":10000,"totalPage":100}

 

[John_w]

You need a VBA JSON parser, for example either https://github.com/omegastripes/VBA-JSON-parser/blob/master/JSON.bas or https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas. In both cases copy everything except the first line (Attribute ...) into a standard module. Both parsers parse a JSON string and produce a data structure of Collections and Dictionaries which makes it is very easy and natural to reference the parts of the data you need.

There is sample code showing how to call the omegastripes parser at https://www.mrexcel.com/forum/excel...n-data-real-time-post5045963.html#post5045963 which you can adapt for your requirements.