Import Json to excel and export excel to Json (Updated 2022)

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. If you’re new to JSON then read JSON tutorial for beginners

Prerequisites

  1. Save your excel file as Macro-Enabled workbook (Refer screen shot below)
  2. Enable macros if they are not already enabled. You can enable it by cliking on file > options > Trust Center > Trust Center Settings > Enable all macros

Save as xlsm
Enable excel macros

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)
  4. Add a reference to MSXML (Tools > references)
    Select appropriate version based on your PC :
    1. Microsoft XML, v 3.0.
    2. Microsoft XML, v 4.0 (if you have installed MSXML 4.0 separately).
    3. Microsoft XML, v 5.0 (if you have installed Office 2003 – 2007 which provides MSXML 5.0 for Microsoft Office Applications).
    4. Microsoft XML, v 6.0 for latest versions of MS Office.

excel to json
Add reference microsoft xml and scripting runtime excel

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(2).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

Using array of strings and objects in JSON

One of our readers asked me how to use an array of strings and objects inside the JSON.

Here’s how to do it:

Public Sub exceltojson()
Dim rng As Range, items As New Collection, myitem As New Dictionary, i As Integer, cell As Variant, objectContainer As New Dictionary, arrayContainer As New Collection
Dim j As Integer
'Set rng = Range("A2:A3")
Set rng = Range(Sheets(1).Range("A2"), Sheets(1).Range("A2").End(xlDown)) 'use this for dynamic range
i = 0
For Each cell In rng
myitem("id") = cell.Value
myitem("name") = cell.Offset(0, 1).Value

'tags
tagsString = cell.Offset(0, 2).Value
tagsCollection = getCollectionFromString(tagsString)
myitem.Add "tags", tagsCollection

'ingredients
ingredientsString = cell.Offset(0, 3).Value
weightsString = cell.Offset(0, 4).Value
ingredientsUnit = cell.Offset(0, 5).Value
ingredientsCollection = getCollectionFromString(ingredientsString)
weightsCollection = getCollectionFromString(weightsString)

j = 0
For Each ingredient In ingredientsCollection
objectContainer("ingredientnaam") = ingredient
objectContainer("eenheid") = ingredientsUnit
objectContainer("hoeveelheid") = weightsCollection(j)
arrayContainer.Add objectContainer
Set objectContainer = Nothing
j = j + 1
Next
myitem.Add "ingredienten", arrayContainer

'Reset values
Set arrayContainer = Nothing
j = 0

items.Add myitem
Set myitem = Nothing
i = i + 1
Next
Sheets(1).Range("A6").Value = ConvertToJson(items, Whitespace:=2)
End Sub

Function getCollectionFromString(val)
getCollectionFromString = Split(val, ", ")
End Function

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.

Related articles :

If you have any questions or feedback, comment below and please use CodingisLove Bin for sharing your code.

Ranjith kumar
5 4 votes
Article Rating
Subscribe
Notify of
guest

430 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Sourabh Shende
Sourabh Shende
7 years ago

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.

Elizabeth
Elizabeth
7 years ago

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?

Elizabeth
Elizabeth
7 years ago
Reply to  Ranjith kumar

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 [ ]

Elizabeth
Elizabeth
7 years ago
Reply to  Ranjith kumar

You save me)))Thank you so much

ram
ram
2 years ago
Reply to  Ranjith kumar

I am not able to access this Pastebin.com where can I see the code please

James Lunt
James Lunt
5 years ago
Reply to  Ranjith kumar

Can I just say this particular bit of parsing has helped me immensly to create some VBA code that will create a Json file with the structure I require for a digital signage system that can use a remote dataset in Json format – Ive been struggling with this for a good 2/3 weeks as Im new to VBA – thank you so much – I now have data coming from Excel into my digital signage system every time the file is saved/updated – really cant thank you enough Ranjith…

Deepak
Deepak
7 years ago

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.

Melih
Melih
7 years ago

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)

Melih
Melih
7 years ago
Reply to  Ranjith kumar

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?

Melih
Melih
7 years ago
Reply to  Ranjith kumar

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.

Melih
Melih
7 years ago
Reply to  Ranjith kumar

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.

hiko
hiko
7 years ago

any difference in dictionary dictionaries to import data, can I select a specific items value

Akash Sarmalkar
Akash Sarmalkar
7 years ago

Hi,

It is showing one error that the ConvertTOJson(items, Whitespace:=2) is not defined while running the code.

https://bin.codingislove.com/aqogiwebaw.vbs

Gragory
Gragory
7 years ago

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?

Gragory
Gragory
7 years ago
Reply to  Ranjith kumar

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.

Gragory
Gragory
7 years ago
Reply to  Ranjith kumar

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.

Amit
Amit
2 years ago
Reply to  Ranjith kumar

Hello Ranjith, This doc really helped me to convert JSON to excel, using inputs from this site I have developed the code but it is giving me output for only upto 7 rows. Here is the code. and JSON output URL.

URL is :https://prices.azure.com/api/retail/prices?$filter=ArmRegionName%20eq%20%27northeurope%27
Below is the code working only for 6 enteries.
Sub Macro1()
‘Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject(“MSXML2.XMLHTTP”)
http.Open “GET”, “https://prices.azure.com/api/retail/prices?$filter=ArmRegionName%20eq%20%27northeurope%27”, False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
h = 1
For Each item In JSON
Sheets(1).Cells(i, 1).Value = JSON(“Items”)(h)(“currencyCode”)
Sheets(1).Cells(i, 2).Value = JSON(“Items”)(h)(“retailPrice”)
Sheets(1).Cells(i, 3).Value = JSON(“Items”)(h)(“unitPrice”)
Sheets(1).Cells(i, 4).Value = JSON(“Items”)(h)(“armRegionName”)
Sheets(1).Cells(i, 5).Value = JSON(“Items”)(h)(“location”)
Sheets(1).Cells(i, 6).Value = JSON(“Items”)(h)(“effectiveStartDate”)
Sheets(1).Cells(i, 7).Value = JSON(“Items”)(h)(“meterName”)
Sheets(1).Cells(i, 8).Value = JSON(“Items”)(h)(“serviceName”)
i = i + 1
h = h + 1
Next
MsgBox (“complete”)
End Sub

Rehan
Rehan
6 years ago
Reply to  Ranjith kumar

Hi, what if the same structure of JSON is hosted on an API URL?

I have {
“status”: 1,
“data”: [//Name, ID, Email Address, Address],
“token”: “XXXXXXXXXXXXXXXXXXXXXXXXXXXX”,
“error”: null
}

How am I going to filter out the Data in the Response JSON text – Should I setup a new Variable like :

http.Send
Set JSON = ParseJson(http.ResponseText)
Set JSON_data = ParseJson(http.ResponseText.Data)
i = 2
For Each Item In JSON_data // then loop throught the information?

If you could share an example on that, it will help alot.

Thanks!

Rajesh
Rajesh
4 years ago
Reply to  Ranjith kumar

Hi.

Thanks for your coding. It’s really useful. But I’m getting “Type mismatch error” also I cannot see any code on this below link. It would be great if you help me with this. My JSON file is having more min 3 to 4 structures in it.
https://bin.codingislove.com/leqidopofu.vbs

how to share my coding and my Json file to you

Gragory
Gragory
7 years ago

Thank you, but now the same error at the string:
For Each Item In JSON(“data”)

Gragory
Gragory
7 years ago
Reply to  Gragory

Sorry, it’s working. Thank you very much!
You are good man 😉

Shadi Salo
7 years ago

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)

Reaules
Reaules
7 years ago

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!

Reaules
Reaules
7 years ago
Reply to  Ranjith kumar

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!

Dylan
Dylan
7 years ago

Based on your example here: http://jsonplaceholder.typicode.com/users, how can you get the same exact details as from your post but only under a certain value which the criteria is “city”: “Gwenborough”?

Mae
Mae
7 years ago

Having trouble with this json file. I can only get 1 carrier. what I would like to happen is that it will give me all of the carriers, Sprint, Verizon, AT&T and T-Mobile, however, I can only get 1.

https://bin.codingislove.com/avijepehal.json

Mae
Mae
7 years ago
Reply to  Ranjith kumar

Here’s the existing code of mine
https://bin.codingislove.com/oxabonadom.vbs

Mae
Mae
7 years ago

Here’s the existing code of mine
https://bin.codingislove.com/oxabonadom.vbs

Mae
Mae
7 years ago
Reply to  Ranjith kumar

Oh sorry. I thought only the loop in which I’m having problem.

Here’s the code: https://bin.codingislove.com/ufajovewug.vbs

Mae
Mae
7 years ago
Reply to  Ranjith kumar

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?

Mae
Mae
7 years ago
Reply to  Ranjith kumar

I’m having an error Invalid procedure call or argument (Error 5)

ioda aka
ioda aka
7 years ago

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

Dre
Dre
7 years ago

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?

Dre
Dre
7 years ago

Hi,

Am getting an error: Err.Raise 10001, “JSONConverter”, json_ParseErrorMessage(JsonString, json_Index, “Expecting ‘{‘ or ‘[‘”)

https://bin.codingislove.com/daqacufavo.json

Whats the problem there?

Dre
Dre
7 years ago
Reply to  Ranjith kumar

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 ‘[‘”)

Ivan
Ivan
7 years ago

Hello Ranjith,

I definitely need your help!
I have the following JSON code to be imported in Excel:

https://bin.codingislove.com/nenezuxese.json

Hope you will help me!
Many thanks

Ivan
Ivan
7 years ago
Reply to  Ranjith kumar

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!

Ivan
Ivan
7 years ago
Reply to  Ranjith kumar

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!!

Ivan
Ivan
7 years ago

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?

Simon
Simon
7 years ago

Hi Ranjith,

Your code is very helpful, unfortunately i’m not able to modify it until i get this json code out of my excel: https://bin.codingislove.com/obunajocim.json

Can you help me please. Thank you very much

Peter
Peter
7 years ago

Hello,

is there a way to use it with Access?

thanks
Peter

catalin
7 years ago

Help!
I’m getting an Error 6 Overflow error, while trying to load a big JSON from an web api

Ragas
Ragas
3 years ago
Reply to  Ranjith kumar

Over Flow Error: I see I value = 32767

https://bin.codingislove.com/lejadupoco.vbs

Jenifer Jain
Jenifer Jain
9 months ago
Reply to  Ranjith kumar

Could you please post the solution?

Lalla
Lalla
7 years ago

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”
}
}
}

Roman
Roman
7 years ago

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
}
]
}
]

Roman
Roman
7 years ago
Reply to  Ranjith kumar

Hi Kumar !
could you show how to change and where? if not difficult, please and thank you!

Roman
Roman
7 years ago
Reply to  Ranjith kumar

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

Emrah
Emrah
7 years ago

Hi Ranjith kumar,
Perfect macro. But my structure is different like below.
I can’t get datas from below link with your code:
http://finans.mynet.com/borsa/anlikborsadata/data.fdata/

If you can help me you can save my life!!

Thank you for you answer,

Emrah
Emrah
7 years ago
Reply to  Ranjith kumar

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

Emrah
Emrah
7 years ago
Reply to  Ranjith kumar

Waww it works. You’re the best!
You really saved my life.
Thank you very much Ranjith.

Emrah
Emrah
7 years ago
Reply to  Ranjith kumar

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.

Julie
Julie
7 years ago

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"
                }
            ]
        }
    ]
]
Julie
Julie
7 years ago

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

Ryan Clouse
Ryan Clouse
7 years ago

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?

Ryan Clouse
Ryan Clouse
7 years ago
Reply to  Ryan Clouse

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

was just me trying some troubleshooting.

Ryan Clouse
Ryan Clouse
7 years ago
Reply to  Ranjith kumar

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

NG
NG
7 years ago

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!

NG
NG
7 years ago
Reply to  Ranjith kumar

Now that I’ve imported several JSON files and analyzed them, I do have a question. Some of the Unicode characters are being lost in the import process. For ex, Ü is being interpreted as Ãœ. Is there something I can change in JsonConverter.bas to fix this?
Thank you once again.

NG
NG
7 years ago
Reply to  Ranjith kumar

The JSON file is encoded with UTF-8 charset.
I was trying to open that file for parsing using FileSystemObject as shown in your example above. Unfortunately FSO cannot read UTF-8 files, that’s why it was unable to read the extended chars.
I changed the vba to use ADODB stream instead (code below). Now it works beautifully. Cheers!

Set objStream = CreateObject(“ADODB.Stream”)
objStream.Charset = “utf-8”
objStream.Open
objStream.LoadFromFile (FName)
jsontext = objStream.ReadText()

semelin2a
semelin2a
7 years ago

Hi,

What if the JSon file contain this kind of data

{“website”: “www.yahoo.com”}
{“website”: “www.google.com”}
{“website”: “www.facebook.com”}

how can i parse the multiple line of data?

Tom
Tom
6 years ago

Hi,

I would like to retrieve Apple app-store rating data, through the following code.
However, whatever i try, i dont get values returned (app defined or object defined error)

Can you maybe help?
Thanks!
Tom

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "https://itunes.apple.com/NL/rss/customerreviews/id=888975587/sortBy=mostRecent/json", False
http.Send
MsgBox (http.responseText)

Set JSON = JsonConverter.ParseJson(http.responseText)

i = 2
For Each Item In JSON("feed")("entry")
Cells(i, 1).Value = Item("im:rating")
i = i + 1
Next
MsgBox ("complete")
End Sub
Ariel
Ariel
6 years ago

Hi, my code have name of array:

{"Tickets":[{"Cuenta asignada":"VICTOR MARIO COFRE OLIVARES","Fecha Trabajo":"11-04-2017","Rut(T\u00e9cnico)":"12099539-1","N\u00b0 de Recurso":"100172757","1ra Hora Trabajo":"10:16:15 AM"},{"Cuenta asignada":"ADRIAN GARRIDO","Fecha Trabajo":"11-04-2017","Rut(T\u00e9cnico)":"16086912-7","N\u00b0 de Recurso":"100003290","1ra Hora Trabajo":"09:58:21 AM"}]}

Can you help me?
Thanks!

Ariel
Ariel
6 years ago
Reply to  Ariel

I already resolved it whit this

https://bin.codingislove.com/leqidopofu.vbs

thanks!!!

Ariel
Ariel
6 years ago

Amigo,
el origen de mis datos se actualiza constantemente, si tengo el libro excel abierto y ejecuto la rutina no se actualiza. Cuando cierro y abro libro actualiza perfectamente, pero necesito actualizar la data con el libro abierto. ¿cómo puedo actualizar la data nueva sin cerrar el libro excel?

Mi english is poor, I hope you help me.
Thanks!!

Ariel
Ariel
6 years ago

Friend,
The origin of my data is constantly updated, if I have the excel workbook open and run the routine is not updated. When I close and open the book it refreshes perfectly, but I need to update the data with the open book. How can I update the new data without closing the excel workbook?

Mi english is poor, I hope you help me.
Thanks!!

Ariel
Ariel
6 years ago
Reply to  Ranjith kumar

The link is private, No access from outside

Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP.6.0")
http.Open "GET", "http://middleware:8802/gettkt.php?report_id=100894&filter=1", False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON("Tickets")

Hoja140.Cells(i, 1).Value = Item("Territorio")
Hoja140.Cells(i, 2).Value = Item("Cuenta asignada")
Hoja140.Cells(i, 3).Value = Item("Rut Tecnico")
Hoja140.Cells(i, 4).Value = Item("1ra Hora Trabajo")

i = i + 1
Next
'Call actualizaTD
End Sub

Thanks”

Eduardo
Eduardo
6 years ago
Reply to  Ranjith kumar

Hi Kumar.
i have the same problem as Ariel. Can you hlep me?
This is the code, with the URL.

Public Sub POLLTC()

Dim http As Object, JSON As Object, i As Integer

Set http = CreateObject("MSXML2.XMLHTTP.6.0")

http.Open "GET", "https://poloniex.com/public?command=returnTicker", False
http.Send

Set JSON = ParseJson(http.responseText)

i = 6

Set Item = JSON("USDT_LTC")

Sheets("API").Cells(i, 5).Value = Item("last")
Sheets("API").Cells(i, 6).Value = Item("highestBid")
Sheets("API").Cells(i, 7).Value = Item("lowestAsk")
Sheets("API").Cells(i, 8).Value = Item("percentChange")
i = i + 1

Set http = Nothing
Set JSON = Nothing

End Sub

thanks!

Nestor Boscan
Nestor Boscan
6 years ago

Hi

How do you create those nice animated images?

Krishna
Krishna
6 years ago

How do I check whether an Item is available inside the JSON object? I’ve been struggling with this for so long..THANKS

krishnakumar
krishnakumar
6 years ago
Reply to  Ranjith kumar

excellent thanks

Krishna
Krishna
6 years ago
Reply to  Ranjith kumar

In the following JSON, I want to check if the Order has a Billing Address and a Address 1 inside Billing Address.

{
  "orders": [
    {
      "id": 5070224324,
      "email": "[email protected]",
      "closed_at": null,
.....
"billing_address": {
        "first_name": "Dhiksha",
.....

however if i use JSON.exists(“orders”)(i)(“billing_address”)(“first_name”) I get a type mismatch error..how can I check if a nested field like this exists in the JSON?

krishnakumar
krishnakumar
6 years ago

In the following JSON,

 
{
  "SalesOrderDetails": {
    "MessageDateTime": "2016-08-16T03:27:50",
    "AccountNumber": "5116082",
    "OrgID": "5116082",
    "Order": [
      {
        "OrderHeader": {
          "SalesOrderNumber": "1003897609",
          "OrderStatus": "Backordered",
          "IsOnHold": "N",
          "HoldReasonCode": "",
          "LastChangeTimestamp": "2017-05-09T06:51:12-04:00",
          "OrderDateTime": "2016-07-25T22:27:50-04:00",
          "OrderNumber": "KRIS_API_SO_TEST_006",
          "OrderReferenceNumber": "",
          "SalesChannel": "DeC",
.....

how do I get to the OrderDateTime? I have tried all possiblities but couldn’t get to it..

Krisjna
Krisjna
6 years ago
Reply to  Ranjith kumar

Ok I understand it now..thanks a lot..

Syamal
Syamal
6 years ago

Hi Ranjith,

Excellent code and support. This was very helpful for my project.

I have a situation where some of the nodes are missing in the JSON structure from time to time and the parsing crashes with “Object Required” error when it hits the absent nodes. I need to address this issue. Can you please help me?

Thank you

mark
mark
6 years ago

Will this work on Windows server 2012 w/ Excel 2013? I am getting “User-defined type not defined” error with a statement containing “MSXML2.XMLHTTP”. Exact same code works perfectly fine with Excel 2013 and Excel 2007 running on Windows 7 pro machines.

Syamal Nayak
Syamal Nayak
6 years ago

Hi Ranjith,

Many thanks for pointing me to the right direction. I removed error-handler and checked for item existence. Now everything works great.

Krishna
Krishna
6 years ago

Hi, one of the APIs I use doesnt give a valid error message but instead simply returns a [] when the request parameter is unavailable.

How do I check this before trying to get a specific value from the dictionary?

Almir
Almir
6 years ago

Reading JSON from a file

Hello,
I am reading JSON from a. But when I enter data in excel, the texts contained in Json do not convert special characters, such as í, ã, á, á, à, ç.

If you read directly a string, such as: json = “{‘Name’: ‘Sebastião’, ‘Age’: 39, ‘Children’: [{‘Name’: ‘Patricia Áquila & Guimarães’ {‘Name’: ‘joão ávila hunter’, ‘Age’: 10}]} ”

So I get the necessary result. But if I pass:

Dim JsonTS As TextStream
Set JsonTS = FSO.OpenTextFile ("C: \ results.json", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
Set json = ParseJson (jsonText)

Returns invalid character data, such as: Agrícola (Agrícola), Agência (Agência), Autorizações(Autorizações)…

Almir
Almir
6 years ago
Reply to  Ranjith kumar

Perfect Ranjith!!!
Good job. Continue with this work, you will be recognized by him.pois is of great importance to the community ( your help arrives in Brazil 🙂 ). thank you.

Almir
Almir
6 years ago
Reply to  Almir

To record, my coding:

Dim json As Object
Dim JsonTS, JsonText
Set JsonTS = CreateObject("ADODB.Stream")
JsonTS.Charset = "utf-8"
JsonTS.Open
JsonTS.LoadFromFile ("C:\example.json")
JsonText = JsonTS.ReadText()
JsonTS.Close

Set json = JsonConverter.ParseJson(JsonText)
Tom
Tom
6 years ago

Hi Ranjith,

I have been trying to convert the linked JSON to excel, but have been failing. I think I understand it to be an object, inside an object, inside an object.

https://bin.codingislove.com/uyukiqasuf.json

If you can offer any advice it would be great.
Cheers

David
David
6 years ago
Reply to  Ranjith kumar

I have a very similar situation to this post and I am getting the error message “Dictionary key not found: 1. Information”. My code is below.

Function AVAPI(Symbol As String)
Dim http As Object, JSON As Object, i As Integer, day As Variant, temp As Dictionary, mydata As Dictionary
Set http = CreateObject(“MSXML2.XMLHTTP”)
http.Open “GET”, “https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=MSFT&interval=1min&apikey=demo”, False
http.Send
Set JSON = ParseJson(http.responseText)

‘ Prepare and write values to sheet
Dim Values As Variant
ReDim Values(JSON(“values”).Count, 6)

i = 0
Set mydata = JSON(“Time Series (1min)”)
For Each day In mydata.keys()
Set temp = mydata(day)
Values(i, 0) = day
Values(i, 1) = temp(“1. open”)
Values(i, 2) = temp(“2. high”)
Values(i, 3) = temp(“3. low”)
Values(i, 4) = temp(“4. close”)
Values(i, 5) = temp(“5. volume”)
i = i + 1
Next

Sheets(“Current”).Range(Cells(1, 1), Cells(JSON(“values”).Count, 6)) = Values

End Function

Any help would be appreciated. Thank you.

EIJAZ SHEIKH
EIJAZ SHEIKH
6 years ago

Hi Ranjit,

Great post!
However, i see you have not explained how to refer to nested OBJECTS OR ARRAYS in a returned JSON string. When i used ParseJSON, it returns only the outer object say if i have this json,
https://bin.codingislove.com/onelitazen.json
I have 2 questions:
1] How do i refer to these nested json objects and arrays, collected in nested Dictionaries and Collections, inorder to properly populate into excel sheet?
2] Will i have to always refer the headers in the json object by names, for populating their values into excel?

Any help will be most appreciated.

Thanks in advance.

siva
siva
6 years ago

hi ranjith,
i am a chip design engineer and newbie to programming. your post helped me to get what i wanted. thanks for this very useful post.
one clarification, the API is getting data for the first time and it maintains it until i make any modification to the code. how do i make sure every time i click the activeX control. it fetches fresh data?
thanks in advance. 🙂

siva
siva
6 years ago
Reply to  Ranjith kumar

thanks a lot for reply ranjith. i added the statement as you suggested but this time around iam not even getting once. i give some old data.
here is my code.

Public Sub exceljson_bcn()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
Set Url = "https://api.coinmarketcap.com/v1/ticker/?limit=2"
Url = Url & "?rand=" & Int((300 - 100 + 1) * Rnd + 100)
http.Open "GET", Url, False
http.Send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("rank")
Sheets(1).Cells(i, 2).Value = Item("name")
Sheets(1).Cells(i, 3).Value = Item("symbol")
Sheets(1).Cells(i, 4).Value = Item("price_usd")
i = i + 1
Next
MsgBox ("bcn complete")
End Sub
siva
siva
6 years ago
Reply to  Ranjith kumar

thanks a lot for correcting it ranjith. it works fine now . really appreciate your help..

Chris Turley
Chris Turley
6 years ago

I have tried your code for the parser but I keep getting mismatch error on the lines with Sheets. reference. Any idea why?

I also have nested data in my json return.
{“data”:{“items”:[{
I tried adding Set Item =Json(“data” ) followed by Set Item = Json(“items”)
I am getting object errors for one or both.

Chris Turley
Chris Turley
6 years ago
Reply to  Ranjith kumar

Absolutely.

I created a class module and and userform per you instructions and included this in a new module1.

https://bin.codingislove.com/momecogixa.vbs

and here is how my json looks.

{
    "data": {
        "items": [{
            "date": "2016-12-31",
            "revenue": 229.93962279194238,
            "impressions": 84677,
            "ecpm": 5.348925811667033,
            "paid_impression": 42988.0
        }]
    }
}
Chris Turley
Chris Turley
6 years ago
Reply to  Ranjith kumar

Sorry for the oversight. It seems to work perfectly. I really appreciate your help. You are a life saver.

Aung Khaing Hein
Aung Khaing Hein
6 years ago

Hello, RANJITH KUMAR,

I have tried your code to Parse JSON. But I got Run-time error : ‘424’, Object required while calling the ParseJSON Function. My JSON file is like that :

[
    {
        Id: "Travel_Claim"
        Name: "Travel Claim"
        Description: "To claim travel expenses"
    },
    {
        Id: "Quotation"
        Name: "Quotation"
        Description: "Online Quotation"
    }
]

Could you please help me for my error? Thank you very much.

Aung khaing Hein
Aung khaing Hein
6 years ago
Reply to  Ranjith kumar

Hi, Ranjith kumar,

I’m OK now. Thank you very much for your code.

Tom
Tom
6 years ago

Microsoft excel does import json files!

Gabriel
Gabriel
6 years ago

Thank you SO much! You are awesome!

Xander Court
Xander Court
6 years ago

Just wanted to say a big thank you, this information on JSON and Excel has been incredibly helpful! 🙂

Phil
Phil
6 years ago

Hi Ranjith,
first of all: great work what you are doing here! Thank you so much for your great and helpful articles and comments.
I tried for hours now, to get some code working, but i dont find the right solution. Would be great if you could help me here!

My JSON ist this: https://bin.codingislove.com/ukucelihuc.json
My vba code is this for now: https://bin.codingislove.com/oviyobizij.sql (i have removed the url, because it contains some private information)
It is already working to read the data and put it in the sheet. But it puts the three fields with the data (for example the first: 2067, 0.63, 1498499127) below each other and i try to get them side by side. And after this, the next three values should go one line below and so on.

I red your JSON Master Articel and it helped a lot for understanding. But i dont know exactly how to work inside the vba code with objects or arrays.

Hope you can help me here. Thank you in advance!

Phil
Phil
6 years ago
Reply to  Ranjith kumar

Thank you so much, it is working perfectly! 🙂