Import Json to excel and export excel to Json

JSON (Javascript Object Notation) is the most used data exchange format nowadays. Microsoft Excel doesn’t have built-in support for importing JSON to excel or exporting excel data to JSON.

VBA-JSON is an excellent library for parsing JSON in VBA. Lets see how to handle JSON in Excel VBA.

Getting Started

  1. Download VBA JSON latest version from here

  2. Extract it, open VBA code editor in excel (Alt + F11) and import the library as shown in the gif below.

  3. Add a reference to Microsoft scripting runtime. (Tools > references > select)

excel to json

Import JSON to Excel

This library provides a simple method ParseJson to parse JSON string into a dictionary object which can be used to extract data. Let’s see an example.

I’m using fake data from http://jsonplaceholder.typicode.com/ which is an API service with fake Json data.

We’ll be pulling user data from http://jsonplaceholder.typicode.com/users by making a GET request which responds with Json data.

json sample data - json to excel

Read more about GET requests in VBA here

Next, we’ll parse that Json and import it to excel. Code for importing data looks like this :

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("id")
Sheets(1).Cells(i, 2).Value = Item("name")
Sheets(1).Cells(i, 3).Value = Item("username")
Sheets(1).Cells(i, 4).Value = Item("email")
Sheets(1).Cells(i, 5).Value = Item("address")("city")
Sheets(1).Cells(i, 6).Value = Item("phone")
Sheets(1).Cells(i, 7).Value = Item("website")
Sheets(1).Cells(i, 8).Value = Item("company")("name")
i = i + 1
Next
MsgBox ("complete")
End Sub

Code explanation

  1. First, define JSON as an object and make a GET request to JSON API

  2. JSON data received in the response is parsed by passing it into ParseJson method.

  3. parsed data is converted into a collection of dictionaries.

  4. Loop through the collection to get each user’s details and set its values to the first sheet.

Running above code looks like gif below.

import json demo - json to excel

Reading JSON from a file

In the same example above, If you want to read JSON data from a local file then you can use FileSystemObject to read all text in the file and then pass it to ParseJson method.

Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Set JsonTS = FSO.OpenTextFile("example.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
Set JSON = ParseJson(JsonText)

Export Excel to Json

VBA-JSON provides another method ConvertToJson which can be used to convert excel data into JSON. Here’s an example.

Sample data with Name, Phone and Email is present in second sheet. Let’s convert it into JSON

Code for this looks like :

Public Sub exceltojson()
Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant
set rng = Range("A2:A3")
'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) use this for dynamic range
i = 0
For Each cell In rng
Debug.Print (cell.Value)
myitem("name") = cell.Value
myitem("email") = cell.Offset(0, 1).Value
myitem("phone") = cell.Offset(0, 2).Value
items.Add myitem
Set myitem = Nothing
i = i + 1
Next
Sheets(1).Range("A4").Value = ConvertToJson(items, Whitespace:=2)
End Sub

Code Explanation

  1. First, define rng as range and set it to data range.

  2. ConvertToJson method takes a dictionary collection or array as parameter. So we should pass our data as a collection.

  3. A Dictionary is an object with keys and values just like JSON but doesn’t support multiple items like arrays or collections, so we create a dictionary for each item and push it into an array or a collection.

  4. Define a dictionary and a collection, loop through the range and set each row’s data into myitem

  5. Push myitem into collection and set it to nothing, because we are using the same dictionary to add next row’s data and push it to collection again.

Finally pass items collection to ConvertToJson method which returns a JSON string.

Running above code looks like gif below

export excel to json

Export Excel to JSON file

In the same example above, If you want to export excel data to JSON file then It can be done by opening a file for output by specifying the path of the file and printing data in it. Sample code below, Running this would save a JSON file in the current workbook’s folder.

Public Sub exceltojsonfile()
Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, myfile As String
Set rng = Range("A2:A3")
'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) use this for dynamic range
i = 0
For Each cell In rng
Debug.Print (cell.Value)
myitem("name") = cell.Value
myitem("email") = cell.Offset(0, 1).Value
myitem("phone") = cell.Offset(0, 2).Value
items.Add myitem
Set myitem = Nothing
i = i + 1
Next
myfile = Application.ActiveWorkbook.Path & "\data.json"
Open myfile For Output As #1
Print #1, ConvertToJson(items, Whitespace:=2)
Close #1
End Sub

Export Excel to Nested JSON

Above code can be modified a bit to get a nested JSON as output. Just add dictionary in another dictionary so that it creates a nested JSON. code looks like this :

Public Sub exceltonestedjson()
Dim rng As Range, items As New Collection, myitem As New Dictionary, subitem As New Dictionary, i As Integer, cell As Variant
Set rng = Range("A2:A3")
'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) use this for dynamic range
i = 0
For Each cell In rng
Debug.Print (cell.Value)
myitem("name") = cell.Value
myitem("email") = cell.Offset(0, 1).Value
myitem("phone") = cell.Offset(0, 2).Value
subitem("country") = cell.Offset(0, 3).Value
myitem.Add "location", subitem
items.Add myitem
Set myitem = Nothing
Set subitem = Nothing
i = i + 1
Next
Sheets(2).Range("A4").Value = ConvertToJson(items, Whitespace:=2)
End Sub

Running above code looks like image below

export excel to nested json

Wrapping up

Read official documentation of VBA-JSON here and use VBA-Dictionary for Mac Support.

Complete JSON tutorial here – JSON for beginners

If you have and questions or feedback, comment below.

Get notified when there's a new post.

Need some help? Post your questions on our forum

Author: Ranjith kumar

A CA student by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

95 Thoughts

  1. Hi,

    This code works very well for the flat json structure. My requirement is to have nested JSON structure. Please find below code for example.
    “{
    “primaryContact”:
    {
    “id”: 2
    },
    “subject”: “FishPhone not working”
    }”

    Please let me know if we can achieve something like this using VBA JSON. Help will be much appreciated.

  2. Hello, this is amazing solve for my problem))thank you)
    But I have a little question. If I need to have this structure then I export excel to Json:
    “price”: [{
    “name”: “product”,
    “value”: 100,
    “key”: “pr21”
    }]
    What need I change in VBA code?

      1. Thank, but I need something look like this:
        {
        “id”: “001”,
        “name”: “mark”,
        “minCount”: 10000,
        “price”: [{
        “name”: “product”,
        “value”: 100,
        “key”: “pr21”
        }]
        } And I can’t realize how made it. I always lose [ ]

  3. Work’s well with the given example.Sorry, i am a newbie in VB and I ‘ave got a problem. Unfortunately, My json response is in the format
    {
    “error”: false,
    “details”: [
    {
    “name”: “AKSHAY”,
    “age”: 25,
    “id”: AV123,
    “place”: “BANGALORE”,
    },
    ]
    }
    how do i export to excel for the above mentioned string? i tried with your example and i got “Type Mismatch” error. Please help.

  4. Hi Ranjith,
    I want to export my Excelsheet to a jSon-file, but I can not solve my problem.
    I really like your Code (thank you!), but the problem is that your solution will be saved in a cell and not as a new jSon-file.
    Can you show me with your example how to import the data into a new created jSon-file, please?

    ???(jsonfile) = ConvertToJson(items, Whitespace:=2)

      1. Thank you very much!!
        But if my sheet has two columns with the same heading, the second one is overwriting the first one. How can I solve this problem?

          1. For example:
            ….
            myitem(“Author”) = cell.Offset(0, 10).Value
            myitem(“Comment”) = cell.Offset(0, 11).Value
            myitem(“Author”) = cell.Offset(0, 12).Value
            myitem(“Comment”) = cell.Offset(0, 13).Value

            The content in the columns are different, but the heading is the same. If I start the code, the columns 12 and 13 are overwriting the columns 10 and 11. But I want all the four columns in my json file.

          2. You can just name the second and third author with different names like myitem(“author-2”),myitem(“author-3”) or else you can make authors an array and push all the author values to that.

            Join our gitter channel https://gitter.im/codingislove/Excel
            I can share some code there

          3. For example:

            myitem(“Author”) = cell.Offset(0, 10).Value
            myitem(“Comment”) = cell.Offset(0, 11).Value
            myitem(“Author”) = cell.Offset(0, 12).Value
            myitem(“Comment”) = cell.Offset(0, 13).Value

            The content of the columns are different, but the headings are the same. The columns 12 and 13 are overwriting the columns 10 and 11, but I actually need all columns.

    1. Yes, you can select specific items by using index of that item instead of looping through all items. Do you have some sample data?

    1. Hi Akash,

      Make sure to import VBA-JSON library as showing in getting started section. If you’ve imported and it’s still the same then try using JsonConverter.ConvertToJson(items, Whitespace:=2) instead of ConvertToJson(items, Whitespace:=2)

  5. Hi,

    I have some information in JSON format, it looke like:
    {“data”:[{“id”:1,”name”:”Eredivisie”,”active”:true},{“id”:2,”name”:”Jupiler League”,”active”:true},{“id”:4,”name”:”Bundesliga”,”active”:true}]}

    I copied your code for import data from JSON to Excel, but VBA calls me a run-time error ’13’: Type mismatch at string:
    Sheets(1).Cells(i, 1).Value = Item(“id”)

    what’s the problem? can you help me to type the right code?

      1. thank you, but I have data in JSON format from API of some sport website and I need to import to Excel. I just copied data that I received.

        1. You can just make a HTTP request to your API as shown in the example where I’m getting data from a fake API. Just Replace it with your actual API if your API responds with data on GET request.

          If that API responds to POST request then you can read How to make POST request in VBA

          1. Yes, it responds with GET requests. First time I did it, just replece your link with my actual link, but it doesn’t work. But VBA calls a run-time error ’13’: Type mismatch at string:
            Sheets(1).Cells(i, 1).Value = Item(“id”).
            I’m not a programmer, and I please you to help me, what I need to change in your code.

  6. Hi, can i get the data using item index.

    for example:
    Sheets(1).Cells(i, 1).Value = Item(“id”)
    Sheets(1).Cells(i, 2).Value = Item(“name”)
    Sheets(1).Cells(i, 3).Value = Item(“username”)

    can i use something like this :-

    Sheets(1).Cells(i, 1).Value = Item(1)
    Sheets(1).Cells(i, 2).Value = Item(2)
    Sheets(1).Cells(i, 3).Value = Item(3)

    1. You can use item index if your dataset is an array. For example : You can use index on data like this – [“1″,”Ranjith”,”ranjithusername”]

  7. Hi,

    i read your post and find what i searched for long time.
    Now i have one Problem. I read your comment on deepaks question but dont get behind the structure. I have following json to convert to excel. https://bin.codingislove.com/wakafagoku.json

    Can you pls pls pls give me an example how i got onto distance and durations value and text values?

    Thanks!

      1. Hey, thx for the answer it was a great help! I have one last question. Can you pls tell me how i can all entries in example the routes ? Like this ? JSON(“routes”)(?max?). I tried it with ubound(JSON,”routes”) but it didnt work.
        Thx man!

        1. That data is not structured properly. You have to write multiple loops or access each of them manually with each line of code to get all the data.

          1. Sure got it working however, I’m having blank rows. What I’d like to see is only the criteria type=Airport. What could be the problem in my code?

          2. You are getting an error because you are calling “Lower” property directly on an array. You should specify Index before calling it. Here’s working code – https://bin.codingislove.com/gonisukidu
            But this will get only one carrier, combine this with the previous code I shared with you to get all the carriers. Hope that helps!

  8. Hi,

    Thanks for sharing very usefull for me ! By now i’m looking for a way to put in the worksheet the data parse when you don’t know the json struct which may change between every rest call. I precise that the Json data may include nested arrays. i’ve not seen or understood how to get automatically the list of items keys. I want to put the data in the sheet in a kind of “deep first” logic using items keys in heading.
    A sample code might help.
    Best regards

  9. Hi,

    I am a DBA by profession, not much of a coded programmer, I have a large json file with data that i want to parse to either csv or excel which them i would get it in my sql db.
    I dont know anything about json but i find this page helpful. I was trying to use VBA but didnt get far with that.
    Whats the best way to parse this json file?

    1. If you need full control over the import then above method is the best but you have to write some code. If you don;t want to write code then you can try JSON import which is built-in in Excel 2016 in Data tab. Built-in import might not work perfectly depending on the JSON structure.

      1. This is the JSON string that am trying to parse into excel. – https://bin.codingislove.com/daqacufavo.json Am not sure if the structure is good, this is how i receive it, so it should be fine.

        I modified the code to map the format of the file: https://bin.codingislove.com/gilerekuju.vbs

        The ParseJSON method throws the Expecting error when i run the code:
        Err.Raise 10001, “JSONConverter”, json_ParseErrorMessage(JsonString, json_Index, “Expecting ‘{‘ or ‘[‘”)

        1. Hi, You are not passing the right JSON string first. Use https://bin.codingislove.com/raw/daqacufavo instead of https://bin.codingislove.com/daqacufavo.json You should pass raw JSON string. Also, For loop is not used the right way. There are different methods to parse it. I’ve put few methods here – https://bin.codingislove.com/datawayafo

          Also, read this – https://codingislove.com/json-tutorial-indepth/ to understand the difference between objects and array and when to use loop.

      1. Hi Ranjith,

        I’m trying but I really cannot manage.. I read all your posts but unfortunately I’m not so familiar with JSON and VBA..
        Do you mind showing me at least a part of it?

        Thanks again!

          1. Thanks a lot! You are very kind!!
            Do you think I can put the code all in the same cell?
            That’s what I was asked to do..

            Thanks again!!

  10. Yes, I ve done that and it works.
    Just I would like to put have all the content in the same cell in Excel and not in more cells.
    Do you think is that possible?

  11. Hi Ranjith,

    I need your help please.

    I have about 500 of these collections to create, via api with post method.

    In my Excel i have it like:
    Title column relation condition src
    100 type equals 100 http:\/\/www.ipods.co.za\/image\/data\/products\/2L76BP.jpg
    115 type equals 115 http:\/\/www.ipods.co.za\/image\/data\/products\/2L76BP.jpg
    125 type equals 125 http:\/\/www.ipods.co.za\/image\/data\/products\/2L76BP.jpg
    178 type equals 178 http:\/\/www.ipods.co.za\/image\/data\/products\/2L76BP.jpg

    My Json should be formatted like this:
    {
    “smart_collection”: {
    “title”: “Ipod”,
    “rules”: [
    {
    “column”: “type”,
    “relation”: “equals”,
    “condition”: “Ipod”
    }
    ],
    “image”: {
    “src”: “http:\/\/www.ipods.co.za\/image\/data\/products\/2L76BP.jpg”
    }
    }
    }

  12. hi, super macro!
    but please tell me is it possible from a tabular form (excel to json)
    WBS name DateStart
    1 Work 10.01.2017
    1.1 work 2 12.01.2017

    to make the structure with children
    [
    { “name”:”work”
    “DateStart”:10-01-2017,
    “children”:[
    { “name”:”work2″
    “DateStart”:”12-01-2017
    }
    ]
    }
    ]

          1. Thank you!!!!
            but I can’t figure out how to make it dynamic depending on the level (WBS) to create children

      1. Hi again Ranjith,
        Below link is a site which shows stocks in stockmarket in real time.
        http://finans.mynet.com/borsa/anlikborsadata/data.fdata/
        So, I would like to get Stock data of each stock from Stock table.
        For example: First stock is “ACSEL” , second one “ADANA”
        For me most important part is receiving ACSEL values
        “ACSEL”: [
        “-1”,
        “2,82”,
        “2,82”,
        “2,83”,
        -0.7,
        “2,81”,
        “2,88”,
        2.83,
        “194.601”,
        “551.325”,
        “16:14:42”,

        I will appreciate if you can help me on this subject,
        Thank you again

          1. Just I see one missing point.
            When you import values from Json to excel it’s taking only first connection values.
            If you re-try it’s taking old values instead of actual ones.
            So, basically refresh is not working.

          2. It depends on your API response. VBA doesn’t store anything so it cannot take old values. debug it by checking the response.

  13. Hi Ranjith,
    I am having trouble getting the parser to work with this JSON format. Can you please help? I ‘d be greatly appreciative!
    [
    [
    {
    “_oid”: “Timebox:12476”,
    “Name”: “Sprint 17-09”,
    “_children”: [
    {
    “Name”: “TVL”,
    “Number”: “B-0219”
    },
    {
    “Name”: “Travel”,
    “Number”: “B-049”
    }
    ]
    },
    {
    “_oid”: “Timebox:1477”,
    “Name”: “Sprint 17-10”,
    “_children”: [
    {
    “Name”: “PR Warehouse”,
    “Number”: “B-0050”
    },
    {
    “Name”: “Energy”,
    “Number”: “B-0094”
    }
    ]
    }
    ]
    ]

  14. Ranjith,
    To further clarify, I am looking for the above posted JSON to convert into excel.
    I’ve spent a good part of the last few days trying to get this JSON to Excel conversion using the function JsonConverter.ParseJson, but was unsuccessful.
    I would be thoroughly grateful for your help!
    Thanks again,
    Julie

  15. Howdee – super helpful tutorial. I’m running into an issue that someone mentioned earlier but looks like they forgot to finish following up on. I’m getting the correct JSON string returned from my GET call. However, I’m getting a type mismatch error when trying to write information to a cell.

    My JSON is:

    {“data”:{“ID”:”123456787954275156″,”name”:”project x”,”objCode”:”PROJ”,”percentComplete”:100.0,”plannedCompletionDate”:”2016-08-26T17:00:00:000-0500″,”plannedStartDate”:”2015-12-18T08:00:00:000-0600″,”priority”:0,”projectedCompletionDate”:”2016-10-26T18:26:32:352-0500″,”status”:”CPL”}}

    My VBA script is: (fyi strURL is the api query built within the excel template)

    Sub ReefConnector()
    Dim hReq As Object, JSON As Object, i As Integer

    Dim ws As Worksheet: Set ws = Worksheets(“ReefCriteria”)

    Dim strURL As String
    strURL = ws.[APIurl]

    Set hReq = CreateObject(“MSXML2.XMLHTTP”)
    hReq.Open “GET”, strURL, False
    hReq.send

    Set JSON = JsonConverter.ParseJson(hReq.responseText)
    Sheets(2).Cells(1, 1).Value = JSON(“name”)

    i = 2
    For Each item In JSON(“data”)
    Sheet2.Cells(i, 2).Value = item(“ID”)
    Sheet2.Cells(i, 3).Value = item(“name”)
    Sheet2.Cells(i, 4).Value = item(“objCode”)
    Sheet2.Cells(i, 5).Value = item(“percentComplete “)
    Sheet2.Cells(i, 6).Value = item(“plannedCompletionDate”)
    Sheet2.Cells(i, 7).Value = item(“plannedStartDate”)
    Sheet2.Cells(i, 8).Value = item(“priority”)
    Sheet2.Cells(i, 9).Value = item(“projectedCompletionDate”)
    Sheet2.Cells(i, 10).Value = item(“status”)
    i = i + 1
    Next

    Set hReq = Nothing
    Set JSON = Nothing

    End Sub

    Thoughts?

    1. also – please disregard the line “Sheets(2).Cells(1,1).value = JSON(“name”)

      was just me trying some troubleshooting.

      1. thanks Ranjith,

        I actually couldn’t get that to work either. still got the mismatch error. got around it by first passing it to an array and then putting array information in excel range. See my example below:

        https://bin.codingislove.com/coxawiceme

        I read through your JSON tutorial and was super helpful. Really helped me understand how it is structured.

        Additional question though, I am looking to loop through about 50 of these and pull information for each. I’m able to count the number of loops I need to run by using JSON(“data”).Count. Do I just need to create a For Loop like the below? I tried this approach but does not seem to work.

        https://bin.codingislove.com/wijafamope

        1. If you are going by the count method then you should specify the array index to access an item in array like this – JSON(“data”)(i)(“ID”). (Index starts from 1) Just use For Each loop as mentioned in example code in the article instead of getting the total count and making it complicated.

  16. Ranjith,
    Thank you SO much for this article. It was an immense help to me in converting a jagged (nested) JSON file into CSV.
    I’m amazed at your willingness to share your knowledge in such detail as well as your continued effort in not only answering questions but also providing customized code to people who needed help. Must indeed be your love for coding 🙂
    Keep up the good work!

    1. Glad it helped and thanks a lot 🙂

      Many other blogs explain stuff very briefly just to get views and not help people. But I want to actually help people solve a problem so I write in-detail and help them with customized code too.

      The only sad part is when people ask for help even without reading the article completely or without trying to write some code.

Leave a Reply

Your email address will not be published. Required fields are marked *