Results 1 to 2 of 2

Thread: json to excel VBA??
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unhappy json to excel VBA??

    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}

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,862
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: json to excel VBA??

    You need a VBA JSON parser, for example either https://github.com/omegastripes/VBA-...aster/JSON.bas or https://github.com/VBA-tools/VBA-JSO...nConverter.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-...ml#post5045963 which you can adapt for your requirements.
    Last edited by John_w; Jul 14th, 2018 at 07:16 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •