Coding Tutorials

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

Ranjith kumar September 11, 2022 430 Comments

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.

Get notified when there's a new post by clicking onnotification-bellin bottom left

Need some help? Post your questions on our forum

Ranjith kumar
Latest posts by Ranjith kumar (see all)

430 Replies to “Import Json to excel and export excel to Json (Updated 2022)”

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

    Reply

  3. 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?

    Reply

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

        Reply

      2. 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…

        Reply

  4. 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.

    Reply

  5. 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)

    Reply

    1. Hi, I’ve updated the post with a new section – Export Excel to JSON file. Let me know if that helps.

      Reply

      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?

        Reply

          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?

      Reply

    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)

      Reply

  8. 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?

    Reply

      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.

        Reply

        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

          Reply

          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.

          3. 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

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

        Reply

  10. 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)

    Reply

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

      Reply

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

    Reply

      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!

        Reply

        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.

          Reply

          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?

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

  15. 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

    Reply

  16. 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?

    Reply

    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.

      Reply

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

        Reply

        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.

          Reply

      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!

        Reply

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

          2. You have to paste the code in code editor. Read instructions in getting started section above.

  19. 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?

    Reply

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

    Reply

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

    Reply

          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

        Reply

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

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

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

  26. 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"
                }
            ]
        }
    ]
]
    Reply

  27. 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

    Reply

  28. 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?

    Reply

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

      was just me trying some troubleshooting.

      Reply

      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

        Reply

        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.

          Reply

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

    Reply

    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.

      Reply

      1. 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.

        Reply

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

  30. 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?

    Reply

  31. 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
    Reply

    1. Hi Tom, There are 2 issue with your code. You are trying to set cell value to item(“im:rating”) which is a dictionary object. So it will throw an error. Set it to label value instead – Cells(i, 1).Value = item("im:rating")("label").
      Second issue – First item in that JSON doesn’t have a rating so it will throw an error. Some error handling should be added there. This code should work – https://bin.codingislove.com/efaconogey

      Reply

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

    Reply

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

    Reply

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

    Reply

    1. Hi Ariel, I guess you are pulling data using an HTTP request. Probably the request is cached. Can you share a sample URL to your source?

      Reply

      1. 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”

        Reply

        1. Hey Ariel, I tested it with a link which has data that changes constantly and it is updating correctly. Were you able to solve this?

          Reply

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

          2. Hi Eduardo,

            I used your code and I’m getting updated values every time. I tried running the code every 10 seconds, how often are you running it?

    1. Hi Nestor, I use some tools like RecordIt to create a gif image directly or record screen using any screen recorder and convert it into gif.

      Reply

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

    Reply

      2. 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?

        Reply

        1. Set the dictionary object to a temporary variable and then check if exists in that object.
          Sample code – https://bin.codingislove.com/dinoqokeyo.vbs

          Above code will work only if you are sure that an object named billing_address exists. If you are not sure then you have to make a check for that first.

          Reply

  37. 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..

    Reply

  38. 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

    Reply

  39. 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.

    Reply

  40. Hi Ranjith,

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

    Reply

  41. 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?

    Reply

  42. 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)…

    Reply

      1. 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.

        Reply

        1. 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)
          Reply

      1. 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.

        Reply

  44. 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.

    Reply

    1. Hi Elijah, Have a look at first example code above. Address object in the JSON is nothing but a nested object. You can parse any object in the same way. You can either get the object values using property names or looping through the entire object.

      Here’s some sample code to parse items in your JSON – https://bin.codingislove.com/viwapigemo.vbs

      If you have dynamic object properties then you can loop through object itself like this –

      For Each property In myobj.Keys()
    MsgBox (property & " - " & myobj(property))
Next

      This comment also has example code where I loop through an Object (not array).

      Reply

  45. 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. 🙂

    Reply

    1. Hi Siva, It should actually retrieve fresh data every time but I think some cache is maintained internally in your case. Try adding some random get parameters to the URL so that every time it looks like a different URL and gets new data. Sample code –

      url = "https://bin.codingislove.com/raw/onelitazen"
url = url & "?rand=" & Int((300 - 100 + 1) * Rnd + 100)
      Reply

      1. 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
        Reply

        1. You have to understand the code before implementing it. You already have a GET parameter named limit so ‘&’ should be used to add more parameters. Also, URL is a string so set URL will throw an error. This should work –

          Public Sub exceljson_bcn()
Dim http As Object, JSON As Object, i As Integer, url As String
Set http = CreateObject("MSXML2.XMLHTTP")
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
          Reply

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

  46. 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.

    Reply

      1. 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
        }]
    }
}
        Reply

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

  47. 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.

    Reply

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

    Reply

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

    Reply

  52. Hi Ranjith

    Thanks for a really helpful articles and answers here in commets,

    i would like to import 2 values form the JSON text below into 2 columns in to a ExcelSheet looking something like this:

    1.0 8242018
    1.0 8283152

    I have tried something like this but get errors:

    MsgBox (JSON(“ServiceRequest1”)(“fs_DATABROWSE”)(“data”)(“gridData”)(“rowset”)(1)(“F4611_TRIP”))

    {
  "ServiceRequest1" : {
    "fs_DATABROWSE" : {
      "title" : "Data Browser - [Warehouse Suggestions]",
      "data" : {
        "gridData" : {
          "id" : 53,
          "fullGridId" : "53",
          "columns" : {
            "F4611_PTSK" : "Task Number",
            "F4611_TRIP" : "Trip Number"
          },
          "rowset" : [ {
            "F4611_TRIP" : 1.0,
            "F4611_PTSK" : 8242018
          }, {
            "F4611_TRIP" : 1.0,
            "F4611_PTSK" : 8283152
          } ],
          "summary" : {
            "records" : 20,
            "moreRecords" : false
          }
        }
      },
      "errors" : [ ],
      "warnings" : [ ]
    },
    "stackId" : 1,
    "stateId" : 1,
    "rid" : "asdfasdfasdfasdfasdfadf",
    "currentApp" : "DATABROWSE",
    "timeStamp" : "2017-06-27:07.31.49",
    "sysErrors" : [ ]
  }
}
    Reply

  53. Hi,
    this article is awesome, the best one I found so far regarding JSON / VBA.
    I would like to make a GET request from https://bittrex.com/api/v1.1/public/getmarketsummaries and then use the returned values and assign them to variables.
    How would I do this?
    Also, if I download the VBA-JSON library you mentioned and select it for the module, would the macro be working on other computers or do they all need to download the library to use the excel sheet?

    Thanks!

    Reply

    1. Hi, please share your existing code?

      The macro will work without any additional import on another computer. Just import in once on your computer.

      Reply

  54. Hi ,

    it possible to write the JSON data from .json file to VBA? i want to write some data from json file to vba and show on PPT. it is possilble?

    Reply

    1. Hi, Checkout “Reading JSON from a file” section in the article. You can read a JSON file in the same way.

      Reply

  55. Hi Sir,
    I tried to change this line using using this API:
    http.Open “GET”, “http://api.pse.tools/api/stocks”

    Having detailed output like this :

    {
    "error": false,
    "message": "success",
    "timestamp": "2017-07-21 15:29:59",
    "data": [{
        "symbol": "2GO",
        "name": "2GO Group, Inc.",
        "last": "23.4000",
        "difference": "-0.3500",
        "change": "-1.47",
        "previous": "23.7500",
        "open": "23.7500",
        "high": "23.7500",
        "low": "22.9000",
        "average": "23.0711",
        "volume": "196200",
        "value": "4526555.00",
        "trades": "147"
    }, {
        "symbol": "ABG",
        "name": "Asiabest Group International, Inc.",
        "last": "31.6000",
        "difference": "-0.1000",
        "change": "-0.32",
        "previous": "31.7000",
        "open": "32.0000",
        "high": "32.0000",
        "low": "31.6000",
        "average": "31.8572",
        "volume": "7600",
        "value": "242115.00",
        "trades": "11"
    }]
}

    I also modify this line with:

    For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("symbol")
Sheets(1).Cells(i, 2).Value = Item("name")
Sheets(1).Cells(i, 3).Value = Item("last")
Sheets(1).Cells(i, 4).Value = Item("difference")
Sheets(1).Cells(i, 5).Value = Item("change")
Sheets(1).Cells(i, 6).Value = Item("previous")
Sheets(1).Cells(i, 7).Value = Item("open")
Sheets(1).Cells(i, 8).Value = Item("high")
Sheets(1).Cells(i, 9).Value = Item("low")

    Apparently I got mismatched error when running the macro.
    Appreciate you utmost help. Thank you!

    Reply

  56. Hi Sir,

    I wonder how I may use your code to adopt on this Api link: http://api.pse.tools/api/stocks

    having this sample output:

    data":[{"symbol":"2GO","name":"2GO Group, Inc.","last":"23.4000","difference":"-0.3500","change":"-1.47","previous":"23.7500","open":"23.7500","high":"23.7500","low":"22.9000","average":"23.0711","volume":"196200","value":"4526555.00","trades":"147"},{"symbol":"ABA","name":"AbaCore Capital Holdings, Inc.","last":"0.3700","difference":"-0.0050","change":"-1.33","previous":"0.3750","open":"0.3750","high":"0.3750","low":"0.3650","average":"0.3674","volume":"1820000","value":"668650.00","trades":"12"}

    Id like to come up with table having the data :(symbol,name,open,high,low,average,volume)

    hoping for your kind support. thanks!

    Reply

      1. Hi Ranjith,

        The code works perfectly thank you very much!
        One more thing, how do I get timestamp example (2017-07-25 14:41:36)
        be included/placed to Range(“H1”)?

        Cheers!

        Reply

          2. hi Ranjith,

            I have encountered slight error when running the macro.
            When I tried to manually run macro every 30secs, it seems that my timestamp is still the same as the first result I got. I assume that i doesnt resend new update if I run it multiple times. is there a workaround for this? thank you very much!

          5. Looks like their site is down, Also change URL line to

            url = url & "?rand=" & Int((300 - 100 + 1) * Rnd + 100)

            and post your further queries on forum.

  57. Hi Ranjith,

    I try to adapt your code to an API although i keep receiving an error “Object doesn’t support this property or method ” at line For Each Item in JSON.

    Do you know what can be the cause?

    This is my adapted code:

    Public Sub exceljson()

Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://livescore-api.com/api-client/scores/live.json?key=lVBPPSOY1CJYjOi4&secret=h0I8s0n0lA7UI1C4XdTM6IL7kv6DjoHB", False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("id")
i = i + 1
Next

MsgBox ("complete")

End Sub

    Thank you!
    Mike

    Reply

      1. Hi Ranjith,
        I am also facing same issue. May I know why “Object doesn’t support this property or method” error is coming. I am trying to run the same code given by you.

        Reply

  59. Thank you so much for this article and sharing your code!

    I followed your instructions and have the JsonConverter module and the Microsoft XML, v6.0 reference. Then I copied your “exceljson” Sub under “Import JSON to Excel” without changes to the code or url.

    When I run the code, I get a “Compile error: Variable not defined” and Item is highlighted in “For Each Item In JSON”. Having read some other comments, I tried adding “JsonConverter.” before “ParseJson(http.responseText)” but recieve the same error.

    I’ve also tried removing “Option Explicit” before the Sub but then I recieve a different error “Run-time error ‘0’: KeyNotFoundError Dictionary key not found: id”. Clicking debug, the code errors at “json_ParseObject.Item(json_Key) = json_ParseValue(json_String, json_Index)” in the “json_ParseObject” function.

    Would you please be so kind as to help me identify what is going wrong?

    Reply

    1. Hi Martin, Removing option explicit should fix the “Variable not defined” error but I’m not sure what’s the cause for the next one. Check if you are getting response from the API or not by adding Msgbox(http.responseText)

      Reply

    1. Hi Pavel, I guess you are talking about the format of SensorValue. Setting the cell format to text before setting the value should solve the issue.

      Sheets(1).Cells(i, 1).NumberFormat = "@"
Sheets(1).Cells(i, 1).Value = item("SensorValue")
      Reply

      1. Yes, that’s right, you helped me a lot. You would not be able to tell how implement the auto execution of this code? For example, with a periodicity of 30 seconds and not using a custom call.
        If it is possible to implement it with the help of excel + vba. For any information I will be very grateful to you.

        Reply

  61. I have a question, why when I use such a code, with a request for a button like you. Data does not change even if they have already changed on the destination server.
    Those. Works once, and then the data is not updated when the macro is run again. Why?

    Reply

  62. Hi Ranjith. Thanks for all the tutorials. I’ve read through everything you’ve posted and I have a question about arrays. I’ve got an API response like below:
    https://bin.codingislove.com/ebaveragek.json

    My code works but I can’t get targetLangs from the end of the sample. I suspect it has to do with the untitled array.
    I found this in your JSON discussion:
    Note: If you are using VBA-JSON in Excel VBA then it converts arrays into collections whose index start with 1 and can be accessed like this – namesList(1)

    But I wasn’t sure how to add it to my code. Suggestions?

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

    Reply

  64. Hey, I’m pretty new to VBA, everytime I’m trying to run a macros I’m facing “User-defined type not defined” error. Can you direct me? I’m trying to convert Excel to JSON using your test data.
    Thanks!

    Reply

    1. Hi Alexandra, You might have missed a reference to Microsoft scripting dictionary. Make sure to add a reference to scripting dictionary as explained in the article.

      Reply

  65. Hey. I followed your steps. But when I run save the VBA.. it says “It cannot be saved in a macro free worksheet” I am a noob in coding. So I do know that I missed something.
    Can u tell which step did i miss.

    Reply

    1. Hi Divyanshu, You have to save the workbook as macro enabled workbook. File > Save as > Select Excel Macro-Enabled Workbook (*.xlsm) in Save as type > save

      Reply

  66. Hey, I followed your steps but I am getting a compile error: type mismatch, and Private Function utc_ExecuteInShell(utc_ShellCommand As String) As utc_ShellResult, is highlighted in yellow. Basically, I want to convert CSV file to nested JSON. I am completely new to VBA programming and don’t know much about it. Could you please help. I am using mac.

    Reply

      1. Hi Ron, That API sends data with Gzip compression. Even after setting Accept-encoding headers, The server is not sending uncompressed data. So you have to use some compression library if you want to use XMLHttp method. But the simplest solution would be to use IE for pulling the API data. Browsers can uncompress gzip so it just works. Here’s the code – https://bin.codingislove.com/yokaqalaxi.vbs

        Also, Add a reference to Microsoft Internet controls, Microsoft HTML object library. Hope that helps!

        Reply

  68. Hi Ranjith,
    It’s a great post. I have been looking for a solution for couple of days now and finally saw your post. It seems simple and doable but I have some questions which I am not sure if your post will help me resolve them.
    I have a query in access which is a combination of fields from three different tables. So, one of the fields in the query is an address field and is in json format. This is how it looks like
    Address
    “[
    {
    “”street””: “”678 Willedrobe Dr.””,

    “”state””: “”MI””,
    “”zipcode””: “”34567″”,

    “”city””: “”Indiana””
    }
    ]”
    Each address field can have multiple addresses. So all I need to do is split the address into 4 different columns(city, state,street,zip). Any ideas how to fix this? Will your post help in fixing this? Thanks for any help in advance.

    Reply

    1. Hi Meno, You can parse that JSON same way as shown in above examples. Here’s some sample code to access data from an AccessDB table and parse JSON in address field – https://bin.codingislove.com/regunasinu.vbs Modify it according to your tables and queries and write some code to add new columns using the parsed JSON.

      Reply

      1. Thanks, Ranjith for the prompt response.
        It did work out very well. I am able to parse the address field following your sample code. However, how do I call this function to execute? On address field? I need to add new columns to the query itself, can it be done?
        Thanks again.

        Reply

        1. Ranjith, I have figured out the above questions myself. I am now able to call the function through field expressions. I am passing address field to parse and works well. Now, I am dealing with the next step and definitely need your help with this. My json address field can contain multiple addresses that means address field contains array of json items in it. so far my code is processing only the first item/ address in the array and appending those fields to the existing query. How do I pick the second item i.e second address and create a duplicate record with all the other non-json format fields along with the parsed json item.
          Here is my code so far:
          https://bin.codingislove.com/molosusani.sql

          Thanks a ton. Appreciate it.

          Reply

          1. I don’t have experience working with access DB but this is how you might do – Add street, city, zip code, state columns manually using access interface, then use the code that I gave in the last comment which loops through each row in the table then add few more lines like dataset.Fields(“street”).Value = json(1)(“street”)

            Once all columns are filled, just delete the entire address field manually using access interface. The only thing you need to work on is – The code that I gave opens a read-only connection. Figure out a way to make that connection write enabled.

  69. hi, i’m trying to use it but it gives me an error “91” i tried all versions of code but the same error has ocurred

    Reply

    1. Hi Mike, Did you add a reference to Microsoft scripting runtime? Press debug on the error dialog and see which line has the error.

      Reply

  71. Hi Ranjith,

    Coudl you help me about the followingbeceause I got stuck a project. I have been looking for a solution for couple of days how can i read these JSON file in visual studio and after this i have to export it to excel:
    This is how it looks like:

    {
    "_id" : ObjectId("595f370eae2a5019070709e5"),
    "location" : {
        "X" : 19.0,
        "Y" : 1118.0,
        "Z" : 149.1
    },
    "model" : 10101,
    "robotname" : "372a",
    "schedule" : 1,
    "welderid" : 13091,
    "weldid" : 24509
},
{
    "_id" : ObjectId("595f370eae2a5019070709e6"),
    "location" : {
        "X" : -11.0,
        "Y" : 1594.5,
        "Z" : 126.14
    },
    "model" : 10101,
    "robotname" : "372a",
    "schedule" : 2,
    "welderid" : 13091,
    "weldid" : 24531
}

    But these attributes i have a lot( about 300). Could you help me how can i read this JSON file?

    Thank you for your time and answer.

    Best Regard, Mate

    Reply

  72. Hi Ranjith,

    Thanks for this great post!
    I am using Access and trying to find a simple way to convert Json data to CSV so that I can import the data easily into a table within Access.

    I have been playing around trying to get the code to work without any luck.

    basically I have the Json data in a file called “test.json” and would like to output the data into a file called “output.csv” with column headers…..

    I have tried a version of the code you posted for reading from a file but as I am a newbie with VBA, I’m at a loss.

    Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Dim JsonText As String
Dim Json As Object
Set JsonTS = FSO.OpenTextFile(“c:\Rubbish\Test.json”, ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close
Set Json = ParseJson(JsonText)

    Any help you can provide will be greatly appreciated.

    Cheers
    Greg

    Reply

    1. Hi Greg, If your JSON file is simple then just use some online tool like – https://konklone.io/json But if you have a complex JSON and need some customization then you have to write some code based on the requirement. Try that online tool and see if it does the job. I’ll write a detailed tutorial on converting JSON to CSV soon!

      Reply

      1. Thanks for prompt reply.

        I am trying to automate the entire process so I can’t use any online tool as I need convert the json data to CSV to make it easier to import into a table within an Access environment.

        Reply

        1. Hey Greg, I’m writing a full detailed article on converting JSON to CSV. I’ll publish it tomorrow. Subscribe to notifications to get notified!

          Reply

  73. Error compiling. VBA highlights this line:
    “Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Dictionary”

    Any idea?

    Reply

  74. Hi Ranjith,

    Thank you very much for awesome article about JSON integration into Excel!

    I need your help – I also read your article about making XmlHttpRequests using VBA.

    However, I’m somewhat stuck with my project – I don’t know and understand how to send request header.
    For testing JSON I’m using Postman (Chrome extension) . There I can include header with “auth” parameter and my API key. There is no basic or any other authorization, but for making requests just API key need to be passed as “auth”. Adding that key to URL is not possible – or to be more precise it will not work.

    So the question is how to add it in order to make GET request?

    Here is the code:

    Public Sub exceljson()
Dim http As Object, JSON As Object, i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "/api/articles.json?limit=10", False
http.send
Set JSON = ParseJson(http.responseText)
i = 2
For Each Item In JSON
Sheets(1).Cells(i, 1).Value = Item("created_by")("name")
Sheets(1).Cells(i, 2).Value = Item("created_at")
Sheets(1).Cells(i, 3).Value = Item("title")
i = i + 1
Next
MsgBox ("complete")
End Sub

    Thank you!

    Reply

    1. Hi George, Did you check Request headers section in xmlhttp article? You can add your auth header like this – http.setRequestHeader "auth", "yourapikey"

      Reply

  76. Hello Ranjith,

    is there any possibility to store a parsed text in some sort of memory buffer in order to not to repeat a request every time?

    Help will be much appreciated!

    Reply

    1. you can store it in a string variable if you want to reuse it in the same run. Or You can just write the parsed text to any cell in excel sheet.

      Reply

    1. Finally, everything is fine… sorry for the post. i forgot to add the library… too fast…

      But if somebody have a POST to http authentication using the json… ^-^

      Reply

  81. Hi, VERY GOOD tutorial, it’s fantastic. But I had a problem, I have an indefinitely objects, how can I make a loop that reads all objects even though I do not know how many?
    At first, I’m reading like this:

    For Each Item In JSON
Sheets(1).Cells(i, 1).Value = JSON("issues")(1)("id")
Sheets(1).Cells(i, 2).Value = JSON("issues")(1)("key")
i = i + 1
Next

    but I’m just reading the first value and several times.
    follows an example from my JSON:

    {
 "issues": [
        {
            "id": "53878",
            "self": "http://...",
            "key": "PLAYGQ-20"
},
{
            "id": "36122",
            "self": "http://...",
            "key": "PLAYGQ-19"
},
{
            "id": "36122",
            "self": "http://...",
            "key": "PLAYGQ-19"
}, 
...

    Would you know how to help me? Thank you so much!

    Reply

    1. Hi Roberto, This should work –

      For Each Item In JSON("issues")
Sheets(1).Cells(i, 1).Value = Item("id")
Sheets(1).Cells(i, 2).Value = Item("key")
i = i + 1
Next

      Read JSON Tutorial and understand the structure.

      Reply

  82. Hi Ranjith-

    Thanks for the documentation. New to VBA and json it was a great help.
    This being said I’m, running (more or less) against the same problem as others.
    Here’s my file

    {"quantity":50,"price":2.04,"sellDetails":{"side":"Sell","openCloseIndicator":"Open","orderID":"haj03590"},"buyDetails":{"side":"Buy","openCloseIndicator":"Open","orderID":"haj03584"}}

As you can see I've two "nested" values (sellDetails and buyDetails) ... I've tried multiple combinaisons but it is  not working

Try .... 
Sheets(1).Cells(i, 31).Value = item("side")  -->  (Nothing printed on the Excel spreadsheet) 
Sheets(1).Cells(i, 31).Value = item("sellDetails")("side") --> runtime error 13 - type mismatch

    Any help will be greatly appreciated !
    Thanks a lot

    Reply

    1. Hi Sophie, Please share your complete code. I don’t know what is Item set to in your code mentioned above.

      Reply

      1. Hi Ranjith
        Thanks a lot for your help on this topic. Works like a charm and more than than … I’ve understand the principale behind ! Thanks again

        Reply

  84. Hi Ranjith
    Thanks a lot for your help on this topic. Works like a charm and more than than … I’ve understand the principale behind ! Thanks again

    Reply

    1. Hey Dirk, WinHTTP or msxml2 is not supported on Mac. Try using VBA-Web They have created a workaround to make http requests using some shell function on Mac.

      Reply

  86. Thank you! Excellent tutorial.
    I made one error to include the FALSE parameter on send but then it ran perfectly.
    Love it – video notes: https://www.useloom.com/share/845a9f6855b74169a4eb1b9530b3b1dc

    Question please: I want to extend this tutorial to include a jQuery Grid widget, ParamQueryPro.

    Is read/write possible between Excel and a jQuery Grid?

    If so, can you suggest the best approach to populate same JSON data on a bi-directional read/write between the Excel spreadsheet and the visual Grid?

    https://paramquery.com/pro

    Reply

  87. Hi
    I’m not sure about this step: Add a reference to Microsoft scripting runtime. (Tools > references > select)
    What do you mean by select?
    Also, tried running and got error ‘user-defined type not defined’ line highlighted is
    Private Function json_ParseObject(json_String As String, ByRef json_Index As Long) As Dictionary

    help is appreciated, thanks!

    Reply

    1. Hi Teri,

      In response to your question , “What do you mean by select?”

      I believe Mr. Kumar’s tutorial is instructing to ADD necessary:

      In Microsoft Visual Basic for Applications editor
      Select TOOLS from menu
      In References – VBAProject dialog
      Scroll down to “Microsoft Scripting Runtime”
      Select it by CLICKING the checkbox

      Reply

      1. Thanks J!
        now I have another problem; here’s Json I try to convert:
        “{“version”:”1.0″,”results”:[{“airDate”:”20171013″,”network”:”CMDY”,”market”:”BOST”,”zoneName”:”BOSCE”,”syscode”:”8369″,”placementTypes”:[{“placementType”:”default”,”count”:{“success”:29,”failure”:0}},{“placementType”:”dynear”,”count”:{“success”:104,”failure”:16}}

        the code: Sheets(1).Cells(i, 1).Text = Item(“airDate”)
        gave runtime error ‘type mismatch’; I tried “.text” instead of value, but didn’t work

        placementTypes/0/placementType = “default”
        placementTypes/0/count/success = “29”
        placmentTypes/0/count/failure = “0”
        placementTypes/1/placementType = “dynear”
        placementTypes/1/count/success = “104”
        placementTypes/1/count/failure = “16”

        would the code be: Sheets(1).Cells(i, 1).Text = Item(“placementTypes/0/placementType”) ?

        help is greatly appreciated!

        Reply

        1. Teri, are you still progressing within this tutorial? If so, I am going to complete it myself and wanted to know if you wanted to progress together with any observations or comments within the steps shared by Ranjith in his wonderful tutorial.

          j

          Reply

          1. hi J!
            what tutorial is that? Sure, I would like to work together…I’m new to Json stuff here. I’m still trying to convert Json to Excel, having little success only…stuck on the nested data…tried all sort of ideas based on the pattern of the code…no luck 🙁
            here’s the link to data http://10.54.230.99:8080/runrate/20171013?view=DETAIL

            I have a project and can’t proceed with this obstacle; would really appreciate if you can help out 😀 !

            Please let me know, thanks!

          2. Excellent! I want to accomplish this exact tutorial as well. To determine the best practice of converting CSV / Excel data to JSON. In a few hours, I shall begin this tutorial and share my progress. I hope we both can accomplish our objectives.

  88. Hi Ranjith,
    on the last comment, I was able to fix the type mismatch error; Json text is :

    “{“version”:”1.0″,”results”:[{“airDate”:”20171013″,”network”:”CMDY”,”market”:”BOST”,”zoneName”:”BOSCE”,”syscode”:”8369″,”placementTypes”:[{“placementType”:”default”,”count”:{“success”:29,”failure”:0}},{“placementType”:”dynear”,”count”:{“success”:104,”failure”:16}}

    I want to get values for the following variables:
    placementTypes/0/placementType = “default”
    placementTypes/0/count/success = “29”
    placmentTypes/0/count/failure = “0”
    placementTypes/1/placementType = “dynear”
    placementTypes/1/count/success = “104”
    placementTypes/1/count/failure = “16”

    as you can see, there is a breakdown of ‘placementTypes’ within ‘results’; how do I code so that the values are read accordingly for the above variables?

    help is greatly appreciated ……from anyone!

    Reply

  89. thank you very much, it helped me a lot
    I only have one problem the first time I call the json normal load but if I enter a record in the second load the json does not update apparently it stays in memory the first time.
    sorry for my bad English.
    Might you help me?

    Reply

  90. Hi J

    How are you doing with the tutorial? I’ve looked at the tutorial, but it doesn’t discussed much on JSON–>VBA.
    Have you got a chance to see the data link I sent you? here’s the link>
    http://10.54.230.99:8080/runrate/20171013?view=DETAIL
    I’m having problem on how to retrieve the data from ‘placementType’ downward.
    here’s what I have : https://bin.codingislove.com/vekivolupu.erl

    I know the coding for count,success,failure is not correct, but I don’t know how it should be called… any ideas?

    Thanks!

    Reply

    1. Hi Teri,

      I became overloaded for a brief moment. Shall complete the tutorial and together, we can add to it with your example as well as mine.

      Please allow me a few hours to catch up.

      Reply

      1. Hi J, what do you mean complete the tutorial? Are you writing the tutorial?
        Can we communicate via email…it’s easier I would think.

        Reply

  92. Hi Ranjith,

    I’ve sent you a couple messages, but have not heard back. In case you didn’t, please refer to the comment above. I really need your help on this and I have a deadline to meet and would really appreciate it if you can. Thanks!

    Reply

  93. Hi Ranjith,
    That helps a lot. Thank you so much!

    btw, I have read the tutorial couple days ago, but I don’t think it explains much about how to retrieve data on any given level of arrays…like JSON(“results”)(1)(“placementTypes”), I didn’t know what the ‘1’ mean. Anyway, I played with the codes and now I understand. Thanks again…take care…until next time : ) !

    Reply

  94. Hi Ranjith
    I’ve the following dataset and would like to extract the array “data” in 2 columns

    {“dataset_data”:{“limit”:5,”transform”:null,”column_index”:null,”column_names”:[“Date”,”Index”],”start_date”:”1948-01-01″,”end_date”:”2017-10-01″,”frequency”:”monthly”,”data”:[[“2017-06-01”,57.8],[“2017-07-01”,56.3],[“2017-08-01”,58.8],[“2017-09-01”,60.8],[“2017-10-01″,58.7]],”collapse”:null,”order”:”asc”}}

    Can you help? Thx.

    Reply

  95. I’m having problem parsing the following JSON: I need to retrieve the different keys:

    {“tasks”:[{“key”:”Value 1″,”content”:”INSERT”},{“key”:”Value 2″,”content”:”INSERT”},{“key”:”Value 3″,”content”:”INSERT”}]}

    I would be glad for any support.

    Reply

  98. Hey Ranjith,

    first of all a big thank you for your code, it is working fine.
    I want to convert a few excel cells into a json.
    This is my code: https://bin.codingislove.com/uganusuwib.vbs
    And this is the JSON i get: https://bin.codingislove.com/nokajuxani.pl

    The problem now is, that as i want to use Firebase, there are too many “{” and “}” in the JSON.. The acceptable result would be this: https://bin.codingislove.com/ujuwepuwip.json

    What am i doing wrong? Do you have any idea? Big thanks in advance and keep up the great work!

    Reply

  99. Ranjith, all of your posts are great. I’m really liking how you lay out your code and explain what each line does line by line. Unfortunately though, on the first example here in the post, when I try and run the sample test code, I get an ‘Access Denied’ error on the http.send line. Do you know if that could be a simple issue or maybe i copied and pasted wrong?

    I’m just getting deep into the whole VBA, macro building, within excel to advance my skill set for web scraping for analysis and I’ve successfully built a scraper that uses the Internet Explorer method which works for awhile, then crashes due to a time lag. I’m hoping that if I convert it to use xml to pull the java data in the background it can run more efficiently.

    Looking forward to your response to this!

    Thanks,
    Abe

    Reply

    1. Hi Abe, Access denied error shows up when you are not allowed to access a site and you get a 503 status code. Check if you are able to access the URL from your browser or not. Coming to your second question, Yes it would be faster to use simple HTTP requests or API calls instead of automating IE. Check out these articles – scrape HTML in VBA, Best practices of scraping a website

      Reply

  100. Ranjith,

    I’ve spent the past few hours reading your posts and trying to understand how I can parse my JSON, but I just can’t seem to figure it out. I am getting a “Type Mismatch” error each time, and I think it might be because I have a nested array. I am not a coder and am not sure where to go from here. Here’s the JSON: https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=MSFT&apikey=demo

    And here’s my Code: https://bin.codingislove.com/mezoxikire.vbs
    Any help would be appreciated! Thanks!

    Reply

      1. Hey Rajinth – thanks for the reply! The code does run (thank you!) but it doesn’t actually drop any data into my sheet afterward. Am I missing some sort of paste function? I re-read the post and I believe it’s supposed to drop the dictionary onto the sheet, right?

        Reply

          1. That absolutely worked, however now I have switched to a mac and am getting an Active X issue. I already installed the new dictionary, as well as VBA-web and that solved my compile issues. Now I am not sure where to go from here. Thoughts? I am using the code you provided, and am on a 2017 Macbook Pro running high sierra.

            Thanks!

  101. Hi Ranjith,

    First off thank you for walking us through this code it has been very helpful for what I am trying to accomplish. However, I am having an issue with my json file containing double quotes. For example, I want my output to look like this:

    {
    "intendedbillmonth": "2018-02-01T05:00:00.000Z",
    "currentdate": "2017-11-28T05:00:00.000Z",
    "opsaccountnumber": "40621018576552",
    "billcycle": 3,
    "accounttype": "Choice Industrial",
    "utilitycode": "CSP",
    "lineofbusinesscode": "Electric",
    "operationalentitycode": "IGSI"
  }

However, it is coming out like this:

{
    ""intendedbillmonth"": ""2018-02-01T05:00:00.000Z"",
    ""currentdate"": ""2017-11-28T05:00:00.000Z"",
    ""opsaccountnumber"": ""40621018576552"",
    ""billcycle"": 3,
    ""accounttype"": ""Choice Industrial"",
    ""utilitycode"": ""CSP"",
    ""lineofbusinesscode"": ""Electric"",
    ""operationalentitycode"": ""IGSI""
  }

    Any idea why it is adding double quotes?

    Reply

  105. My JSON query is returning a single (non-array) result, and therefore doesn’t include brackets. How can I make this work in this way? It currently gives me a Type Mismatch error. Many thanks!

    Reply

    1. Hi, If your result is a not array then don’t use a FOR loop. Use properties directly – JSON("yourProperty") Read JSON tutorial for more details.

      Reply

  107. Hi Ranjith,
    Could you please help me.
    I want JSON format has always changes.
    like this –

    Set Json = JsonConverter.ParseJson("{""name"":"John Smith",""age"":1}")
Can I add item in Json  like that 
 Json .("from")("country")="JAPAN"
 Json .("from")("city")="tokyo"
then like 
{
  "name": "John Smith",
  "age": "1",
  "from": {
   "country": "JAPAN"
    "city": "tokyo"
  }

}

    how can I do something like that ?

    Reply

      1. Hi Ranjith,Thanks so much for your help.
        I have another question.
        For example, I want to create json file in Japanese.
        like this:https://bin.codingislove.com/wujujexove.js

        When compiling, the character code is garbled.
        like this：
        {“name”:”\u5C71\u4E0B”,”from”:{“country”:”\u65E5\u672C”,”city”:”\u6771\u4EAC”}}
        how can I do something like UTF-8?

        Reply

        1. Hi Ranjith,
          It’s working now
          I　solved the problem.
          I do not know if this change is correct

          JsonConverter module
          When in Case VBA.vbString　

          Change before　ConvertToJson = “””” & json_Encode(JsonValue) & “”””
          After change　　ConvertToJson = “””” & JsonValue & “”””
          Thanks so much for your help.

          Reply

  108. Hi! Great work on this project, it saved my day 😉
    However, I have a small problem I can’t figure out to fix. In my sheet I use 2 macro’s to get information from 2 different sites. The first one works perfectly, the values update everytime the macro is run. But the second one only updates the 1st time. I have to close and re-open the sheet to get the values updated again.

    This is the code from the 1st one that runs perfectly: https://bin.codingislove.com/unerahuwuv
    This is the code for the 2nd one that only updates 1 time after opening the sheet: https://bin.codingislove.com/arehacoxij
    As you can see, I used the same kind of code and structure, so I don’t know what’s wrong with it.

    Greetings and thanks in advance, Johan

    Reply

      1. Hi Ranjith, thanks for the fast reply.
        So I changed my code to this:
        https://bin.codingislove.com/gogemipopi.vbs

        The values update every time now (well if they changed on Coinmarketcap) but now there is a more serious problem: I only get values for the first 100 coins anymore, while there should be 1360.
        What should I do now to fix that?

        Kind regards, Johan

        Reply

  109. Hi Ranjith,

    The code you have provided works perfectly, but now I find myself needing to find another key, within the ‘for each property’ loop.

    In the example below the first key for timesheets is 371149608. Your code extracts this key perfectly.
    However, as I go through the different fields in ‘item’ I find yet another set of keys for ‘customfields’

    I have tried a number of different approaches without success. What do you suggest?

    Thanks again,

    Long code snippet removed

    Reply

    1. Sorry, the code I pasted that you gave me was for a different table, so it will probably be a little confusing. The code for this table is:

      For Each Property In JSON("results")("timesheets").Keys()

      Set item = JSON("results")("timesheets")(Property)

      Now within the ‘For each Property’ loop I need to find the keys for ‘customfields’.

      Reply

      1. Ranjith

        I’m attempting to adapt a workbook originally created by Kyle123 on the Excel Bar forum here (http://www.excelbar.com/forum.php?mod=viewthread&tid=9571&highlight=Companies%2Bhouse )

        I’ve created a ChargeList and ChargeListItem class module that broadly mirrors the OfficerList equivalents and managed to get this working to pull out parameters like “total_count” but get stuck doing the same for the parameters in the “Items” object.

        Code in the main Module as follows – works until I get to cl.Offset(, 9).Value2 = charges.Items.charge_code

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

        Reply

  112. Hi Ranjith,
    My Json format is a little bit different like below:
    {“asks”:[[“0.00004272″,8283.29573189]],”bids”:[[“0.00004253″,11.6243558]],”isFrozen”:”0″,”seq”:62313053}

    it’s from this url:
    https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_NXT&depth=1

    I would like to retrieve asks, bids, isFrozen, seq separately and import into excel. But I keep getting run-time error ’13’: Type mismatch.

    Can you help me with the right code? I appreciate your kindly help.

    Reply

    1. Hi DoubleWEI, That site has scraping protection using Google ReCaptcha so you may not be getting the actual response from GET request.

      Reply

    2. Hi Jigar, Just use For Each Item In JSON("bseNseJson")
      Read JSON tutorial linked above and understand JSON structure.

      Reply

  114. How about a POST request? This was super useful for my GET requests but I’m having a lot of trouble with POST requests… The last error message I got was Error 400 Request header or cookies too large.

    I’m super lost. Help me please!

    Reply

      1. I have not been able to add the string request on the body, I get the same error message. I have read that the problem may be that the URL is too long, but I have tried different ways.

        Thank you in advance for checking this!

        Sub POST()

        Dim httpObject As Object
        Set httpObject = CreateObject(“MSXML2.XMLHTTP”)
        Dim sURL As String

        sURL = “https://inventory.zoho.eu/api/v1/items?authtoken=XXXXX&organization_id=XXXXX&JSONString={“”name””: “”BedSheets””}”

        httpObject.Open “POST”, sURL, False
        httpObject.setRequestHeader “Content-Type”, “application/json;charset=UTF-8”
        httpObject.setRequestHeader “Accept”, “application/json;charset=UTF-8”
        httpObject.send

        MsgBox httpObject.Status
        MsgBox httpObject.responseText

        End Sub

        Reply

          1. thank you for taking the time! It’s not working though, I get error message code 4 “Invalid value passed for JSONString”. :/

            I read about content length filed to get rid of this error but it does not seem to work with me either. Also what is this additional URL you added? sURL = “https://requestb.in/1kvcgtk1”? https://bin.codingislove.com/uvaxahikad.vbs

          2. I cannot debug it without the actual auth token. requestbin URL was used to check the request. You can Remove it. Post your further questions on forum

    1. Hi Mohsen, Make sure to add a reference to Microsoft scripting runtime and option explicit is not set in your VBA editor. Also, Use Bin for sharing your code here.

      Reply

      1. Hello again this is my JSON:
        Private code Removed
        and i am using the same VBA call above but replacing it with my URL.
        I am receiving error 10001 as it seems i cannot jump to the second “results” in my JSON file or maybe there is another issue. I am already including the reference to microsoft scripting library.

        Reply

      2. Hi Kumar, i figure it out where is the error. Somehow my json files aren’t accept and when i turned them to text, it worked. However i have thousands of files and need continuous conversion from json to text to be accepted withrout error 10001. what should i correct in the below code?

        Private code Removed

        Notice, the first url work but the second doesnt work at all. What is happening exactly here?

        Reply

        1. The second URL will get you HTML as response and not JSON. So you get an error. You need raw JSON. Where do you have your 1000s of JSON files hosted?

          Reply

          1. Hello again Kumar, unfortunately they are on our intranet but when i open them in firefox, firefox can view them as JSON but when i go throught hedeveloper tools, i can see the html with nested json in it. So i copy the whole body and save it under here:

            Private code Removed

            these are the pages i am dealing with. Do you thing i should load the DOM and then parse the JSON?

          2. Firefox has a default JSON reader. It might format JSON and show it as HTML. You have to check content type being served by your intranet server. Please post on forum further.

          3. Dear Kumar i checked all the xhr loaded when the original page isn’t loading. This is the one that contains the main info. Is there a way to load it as a DOM and then parse the innertext?

          4. Hello Kumar!! the problem was resolved by fixing the cookie issue as we need to authenticate on the page i am parsing. I highly appreciate your assistance on this 🙂

  118. Hi Ranjith,

    Please save me!!!!!!!. Urgent

    I am using HP UFT to automate REST API. I will get the JSON response like below. I want to send this to excel in my local machine path to validate name and stock id with the WEB UI values. But everything at run time. I mean one click to run and validate.

    Could you please send me some code to write in my HP UFT API Testing to convert JSON response to excel sheet at run time. Thanks in advance.

    {“stocks”:
    “name”: “apple”,
    “price”: “125”
    “stockid”: “ST123”
    }

    Reply

  120. Hi Ranjith,

    I tried it as well, but also get the famous runtime error. Here is my code:

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

    Thanks for helping me out and putting me in right direction.

    Oh and a second question. Is it possible to make the GET http string dependant on a specific cell? (Say in my example “39078833” Is in a cell, say A1. So that if I change the input in cell A1, that the GET string changes automatically, and thus the results of the JSON as well?

    Thanks for all you help! Robert

    Reply

  121. Hello, First than all THANK YOU is amazing code.

    I’m trying to get some infomation from Azure pricing calculator API and as this web page show ” https://docs.microsoft.com/en-us/azure/cloud-solution-provider/billing/azure-csp-pricelist ” this API will give some JSON that you can see in the previous page or in this page ” https://bin.codingislove.com/ejemegasel.json “, well when i try to get this information to my worksheet with the next code “https://bin.codingislove.com/ulojivegih.erl”, is poping up Error “invalid argument or procedure call”, can you help me to fix it?
    Thank you

    Reply

  123. Hi,
    I am trying to write JSON in the following format: https://bin.codingislove.com/eqaxuvigov.json

    I am unable to generate the following:

     
“context”:[
               {
                  “varName”:”PowerInput1″,
                  “dataPointXid”:”PowerInput1″,
                  “updateContext”:true
               },
               {
                  “varName”:”PowerInput2″,
                  “dataPointXid”:”PowerInput2″,
                  “updateContext”:false
               },
              {
                  “varName”:”PlantOperation2″,
                  “dataPointXid”:”PlantOperation2″,
                  “updateContext”:false
               }
            ],

    I tried to add a new Collection but there is another problem if I use Collection. Do I need to use Array instead? If so, may I know how to do it.

    Appreciate your guidance.

    Thanks.

    Felicia

    Reply

  124. I am learning VBA for xsl and automating a project.I am able to provide input data to an API which currently works .But i need to provide input data from from a xsl file ,lets say data.xsl which has input data in column 1 to 30 i,e C2 to C30 in sheet 1 . I tried few things but don’t seem to get the right syntax.Can you please help with that?
    I want parameter ‘st’ to read data from a xsl file .
    Here is how i am providing data .

    Dim http As Object
    Dim st As String

    Set http = CreateObject(“MSXML2.XMLHTTP”)

    st = “Site=SBA_Modeler_V30&Data={‘input_BirthDate’:’1991-12-08′,’input_HireDate’:’2012-01-01′,’input_Gender’:’M’,’input_AnnualPlanComp’:33500.22,’input_EmployeeClass’:’Special Risk’,’input_AnnualPayGrowth’:1,’input_MarketPerformance’:’Average’,’input_PVD’:34,’input_NRA’:56,’input_MBAA’:56,’input_Custom_BCD’:45,’input_Custom_TermAge’:27,’input_RemainingElections’:1,’input_Pre2011ServiceYears’:0,’input_ProjectedServiceYears’:1.75,’input_ProjectedBenefitAmount’:0,’input_DROP_LumpSum’:2285.95,’input_ProjBuyBackABO’:0,’input_DateABO’:2018-01-31,’input_ProjectedABO’:2285.95,’input_ProjectedAAL’:0,’input_DateAAL’:1900-01-01,’input_InvestmentBalanceTBA’:0,’input_CurrentABO’:1331.08}”

    http.Open “POST”, “https://beqlb02.poolt.hewitt.com/dsi0042/calculator”, False
    http.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
    http.send (st)

    Reply

  126. Hi Ranjith

    I need to have JSON with both key and value from sheet which were there in 2 different columns And also I want to have them under one bracket . For example I want to have my output like this
    {

    “SFM_FOV_SL_EXCEL_TRUCK_NAME”: “Truck Name”,

    “SFM_FOV_SL_EXCEL_TRUCK_TYPE”: “Truck Type”,

    “SFM_FOV_SL_EXCEL_TRUCK_CLASS”: “Truck Class”,

    }

    Take Key in column A and Value in Column B
    Thanks in advance.

    Thanks
    Lakshmi

    Reply

  127. Thanks for the detailed instructions. In the section “Reading JSON from a file” can you tell me the path where I have to save the “example.json”?

    Reply

    1. Hi Mohmad, Save the example.json in the same path in which your current excel file is saved.

      Reply

  129. Hi Ranjith,
    Thank you for a very informative page. I went through it but unable to get a solution for my requirement:

    I need to pass the json format to as a POST request :
    { “instruction”:”login”, “sessionId” : 0, “json”: { “\”user\””:”\”xxxxx\””, “\”password\””:”\”yyyyy\”” } }

    I am having issues with the “” and the “\” in the nested json within.

    Reply

  132. Hello Rajnith…
    Thanks for this wonderful code… My json output is as below

    {“spSD”:”1244.60|3.7|0.3%|1240.45|1245.00|1237.56|1240.90|1244.60|1244.67″,”spGD”:”15.850|0.063|0.4%|15.768|15.859|15.734|15.787|15.850|15.857″,”spNR”:”68.308|-0.272|-0.4%|68.365|68.441|68.270|68.580|68.308|68.320″}

    How can i work on this because it does’t have key name.

    Reply

  134. Hello…
    Its wonderful code for vba but i am stuck on one json and unable to handle the error.
    I am unable to parse the below json

    {“SG”:”1222.84|1.4|0.11%|1221.28|1223.37|1214.01|1221.44|1222.84|1222.94″,”SS”:”15.557|0.072|0.46%|15.470|15.558|15.361|15.485|15.557|15.567″,”SI”:”68.540|-0.099|-0.14%|68.690|68.700|68.520|68.530|68.540|68.550″}

    Kindly help me for this format as its doesn’t have header name !!!

    Reply

  135. Hi there as many of us are new to JSON within VBA is there any chance you could show the below in using POST method where the post is sending some JSON objects in the request…

    BTW Excellent work!!
    //Petri
    Set http = CreateObject(“MSXML2.XMLHTTP”)
    http.Open “GET”, “https://api.translator.com/detect?api-version=3.0”, False
    http.send

    Reply

  136. Hello,

    I am trying to build a JSON file that includes a string array and it is not working for me.

    The problem is with the ‘notified_to’ data shown in the examples below.

    Any help you can provide would be greatly appreciated. Thanks.

    I have posted my code here: https://bin.codingislove.com/otucaxecox.vbs

    This code generates the following JSON file

    {
    “incoming”: false,
    “private”: true,
    “notified_to”: {
    “0”: “abc”,
    “1”: “xyz”
    }
    }

    However I need it look like this:

    {
    “incoming”: false,
    “private”: true,
    “notified_to”: {[“abc”, “xyz”]
    }
    }

    Reply

  137. Hi.

    Thanks for the samples. My question is when you do not know the field names then how can you automatically read and parse any JSON file?

    Reply

  138. Maybe everyone else is much too intelligent to need this, but perhaps on the page you could add above the code, “to copy the code to Excel, click the arrows which say ‘View Source’ on top right of code window so you can copy it without the line numbers”! Great tutorial thanks!

    Reply

  139. Hi, I just tested the first sub exceljson() in my Excel, running from a button in the sheet, and it failed with “Variable not defined” error for “Item”. I fixed this by adding “, Item as Variant” to the Dim list and it then works. Otherwise I added Option Explicit at top of the file, not sure if this error is me or the code.

    Reply

    1. Hey Will, Option explicit means every variable used in the code has to be explicitly defined first. That is the reason you were getting the error with option explicit.

      Reply

  140. Hi Ranjith,
    Great Post!
    I pasted your code as is and have imported the JsonConverter.bas file by Tim Hall and have enabled Microsoft Scripting.Runtime Library. Somehow, I always get the same error everytime:
    Run Time Error 0: KeyNotFoundError Dictionary key not found;
    I see one other user had also faced a similar problem. Can you please help with this? I have spent more almost 2 days on this, can’t find the fix!

    Regards,
    Pranjal

    Reply

  141. Hi Ranjith,
    On 17th Dec I had posted a query on Runtime Error 0 Dictionary Key not found. I found the solution myself: The Dictionary being used in the JsonConverter is not referred by Tim Hall as Scripting.Dictionary and that was getting confused with the Selenium Dictionary and messing things up; The moment I changed the Dictionary to Scripting.Dictionary at 2 places in the source code, it started working perfectly!

    Regards,
    Pranjal

    Reply

    1. Pranjal,
      Thank you for pointing me in the right direction. Indeed, if you have Selenium in your project, the JsonConverter code will throw that error. I was hunting for hours for this solution. Just to add weight to others who may be searching for an answer, this particular error is: Run-time error ‘0’: KeyNotFoundError Dictionary key not found: (then the first key is the one it chokes on). Just for clarity sake, I had to change “Dictionary” to “Scripting.Dictionary” in the JsonConverter module (2 places at lines 460 and 464 (yeah, I know that may change but it’s still helpful)) and also anywhere in my macro.
      -Richard

      Reply

  142. im trying update ticket from jira with excel, my idea is take id ticket value from input with excel and with visual basic update the specific ticket, can u help me? i only can create tickets but i cant update. Thanks

    Reply

  144. Hi Ranith,

    I’ve a question. I’ve imported the JSONConverter module, and ceated a module with the example code from your article.

    If I do this inside of a workbook (anything.xlsm), it works every time.

    If I do the same by importing the modules into my PERSONAL.XLSB file, I get the following error at line 486:

    https://cl.ly/9443432666b5
    https://cl.ly/ad2de83d5707

    I have scripting run time reference ticked in both examples.

    Any idea why this might be happening? I have literally spent hours looking at it, googling…failing 😦

    Any insight on how I can get the JSONCOnverter to work from inside PERSONAL.XLSB?

    Thanks.

    Reply

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

    Can you please help out

    Reply

  150. Hi Team,
    Thanks for simplifying json handling as explained above. However, I am trying to parse my json and need to get the following Created, udpated on header level. Can you please suggest how to retrieve this.
    {
    “created”: 0,
    “updated”: 3,
    “deleted”: 1,
    “itemsFailed”: 0,
    “results”: [
    {
    “resourceId”: “ABATS”,
    “created”: 0,
    “updated”: 3,
    “deleted”: 1
    }
    ]
    }
    Help will be much appreciated.

    Regards,
    Sourabh

    Reply

  151. Hi Ranjith,

    I’m setting up an Excel spreadsheet that extracts the company’s API data (type rest – “Get”). I got to this code, it runs normally, but VBA does not paste the API information in the “API” Worksheet.
    I will share the code (I will only have to remove the password)
    Could you help me in this process? I’ll be very grateful.

    Sub TesteAPI()
    Dim http As Object
    Dim json As Object
    Dim i As Integer

    ‘A partir da planilha “Dados API” ele conecta a API:
    Dim pm As Worksheet
    Set pm = Sheets(“API”)

    Set http = CreateObject(“MSXML2.XMLHTTP”)

    ‘O VBA acessa a API e realiza a autenticação:
    http.Open “GET”, (“https://api01-qa.nimbi.net.br/API/rest/PurchaseOrder/v4?InitialDate=2020-06-15&FinalDate=2020-06-26”), False

    http.Send

    Set json = ParseJson(http.responseText)

    i = 2

    ‘Aqui ele estrutura as hierarquias do Json em linhas, gerando uma tabela:
    For Each item In json(“PurchaseOrderGroupGetAPI”)
    pm.Cells(i, 1).Value = item(“PurchaseOrder_Id”)
    pm.Cells(i, 2).Value = item(“PurchaseOrder_Title”)
    pm.Cells(i, 3).Value = item(“PurchaseOrder_OrderStatusCode”)
    pm.Cells(i, 4).Value = item(“PurchaseOrder_PaymentAddressExternalId”)
    pm.Cells(i, 5).Value = item(“PurchaseOrder_PaymentAddressDescription”)
    pm.Cells(i, 6).Value = item(“PurchaseOrder_SupplierCompanyTaxNumber”)
    pm.Cells(i, 7).Value = item(“PurchaseOrder_CreatedBy”)
    pm.Cells(i, 8).Value = item(“PurchaseOrder_DocumentFormCode”)
    pm.Cells(i, 9).Value = item(“PurchaseOrder_CreatedDateERP”)
    pm.Cells(i, 10).Value = item(“PurchaseOrder_SupplierCompanyName”)
    pm.Cells(i, 11).Value = item(“ListOrderItems_Code”)
    pm.Cells(i, 12).Value = item(“ListOrderItems_ShortDescription”)
    pm.Cells(i, 13).Value = item(“ListOrderItems_Quantity”)
    pm.Cells(i, 14).Value = item(“ListOrderItems_UnitPrice”)
    pm.Cells(i, 15).Value = item(“ListOrderItems_TotalPrice”)
    pm.Cells(i, 16).Value = item(“ListOrderItems_DeliveryDeadline”)
    pm.Cells(i, 17).Value = item(“ListOrderItems_PaymentTypeDescription”)
    pm.Cells(i, 18).Value = item(“ListOrderItems_UnitOfMeasureCode”)
    pm.Cells(i, 19).Value = item(“ListOrderItems_NatureOfOperationCode”)
    pm.Cells(i, 20).Value = item(“ListOrderItems_DeliveryDate”)
    pm.Cells(i, 21).Value = item(“ListOrderItems_PER”)
    pm.Cells(i, 22).Value = item(“ListOrderItems_RequisitionNumber”)
    pm.Cells(i, 23).Value = item(“ListOrderItems_ListCostAllocation_CostAllocationId”)
    pm.Cells(i, 24).Value = item(“ListOrderItems_ListCostAllocation_AccountAssignmentCategoryCode”)
    pm.Cells(i, 25).Value = item(“ListOrderItems_TaxesOrdemItem_ICMS”)
    pm.Cells(i, 26).Value = item(“ListOrderItems_TaxesOrdemItem_TaxReplacementCode”)
    pm.Cells(i, 27).Value = item(“ListOrderItems_TaxesOrdemItem_PIS”)
    pm.Cells(i, 28).Value = item(“ListOrderItems_TaxesOrdemItem_COFINS”)
    pm.Cells(i, 29).Value = item(“ListOrderItems_TaxesOrdemItem_IPI”)

    i = i + 1
    Next

    MsgBox (“Relatório gerado!”)

    End Sub

    Abaixo segue a estrutura do Json que quero colar no Excel:

    {
      “TotalPurchase”: “4”,
      “PurchaseOrderGroupGetAPI”: [
        {
          “PurchaseOrder”: {
            “Id”: 44942,
            “Title”: “Pedido – teste elvis RC1”,
            “PriorityCode”: “Normal”,
            “IsAddressByOrder”: false,
            “OrderStatusCode”: “Draft”,
            “SupplierCompanyTaxNumber”: “11440813000119”,
            “TotalPrice”: 3.28,
            “TotalQtdItems”: “1”,
            “CreatedBy”: “[email protected]”,
            “CreatedDate”: “2020-06-16T18:09:20Z”,
            “UpdatedBy”: “[email protected]”,
            “UpdatedDate”: “2020-06-16T18:09:22Z”,
            “DocumentFormCode”: “PCCATALOG”,
            “CreatedDateERP”: “1900-01-01”,
            “CompanyCurrencyISO”: “BRL”,
            “BuyerCountryCode”: “BR”,
            “BuyerTaxNumber”: “23643315003097”,
            “BuyerContact”: “[email protected]”,
            “SupplierCompanyName”: “WEBB SERVICOS DE CONSULTORIA EMPRESARIAL LTDA.”,
            “SupplierCompanyCountryCode”: “BR”
          },
          “ListOrderItems”: [
            {
              “Id”: 236752,
              “Code”: “N_10341995-00”,
              “ShortDescription”: “ARQUIVO MORTO POLIONDA OFICIO 350X135X240 AZUL”,
              “DeliveryAddressExternalId”: “2401”,

    Reply

  152. In the first example, let’s say if i have no idea about what the “key” in the collection of dictionary is, how can i figure it out from the JSON object?

    Reply

  154. Hi Ranjit,
    May be you could help
    This is the json file
    {
     “country”: “Andorra”,
     “country_code”: “AD”,
     “list”: [
      {
       “id”: 1,
       “bank”: “ANDORRA BANC AGRICOL REIG S.A.”,
       “city”: “LES ESCALDES”,
       “branch”: null,
       “swift_code”: “BACAADAD”
      },
      {
       “id”: 2,
       “bank”: “ANDORRA GESTIO AGRICOL REIG SAU”,
       “city”: “ESCALDES-ENGORDANY”,
       “branch”: null,
       “swift_code”: “AAMAADAD”
      },
      {
       “id”: 3,
       “bank”: “BANC SABADELL D’ANDORRA S.A.”,
       “city”: “ANDORRA LA VELLA”,
       “branch”: null,
       “swift_code”: “BSANADAD”
      },
      {
       “id”: 4,
       “bank”: “BANCA PRIVADA D’ANDORRA S.A.”,
       “city”: “LES ESCALDES”,
       “branch”: null,
       “swift_code”: “CASBADAD”
      },
      {
       “id”: 5,
       “bank”: “CREDIT ANDORRA,S.A.”,
       “city”: “ANDORRA LA VELLA”,
       “branch”: null,
       “swift_code”: “CRDAADAD”
      },
      {
       “id”: 6,
       “bank”: “MORA BANC GRUP SA”,
       “city”: “ANDORRA LA VELLA”,
       “branch”: null,
       “swift_code”: “BINAADAD”
      },
      {
       “id”: 7,
       “bank”: “MORA BANC SAU”,
       “city”: “LES ESCALDES”,
       “branch”: null,
       “swift_code”: “BINAADB1”
      },
      {
       “id”: 8,
       “bank”: “RESULT INTERNACIONAL SA”,
       “city”: “ANDORRA LA VELLA”,
       “branch”: null,
       “swift_code”: “RINSADA1”
      },
      {
       “id”: 9,
       “bank”: “VALL BANC”,
       “city”: “ESCALDES-ENGORDANY”,
       “branch”: null,
       “swift_code”: “VALBADAD”
      }
     ]
    }
    This is my code and I get an error:
    Dim jsonText As String
      Dim jsonObject As Object, item As Object
      Dim i As Long
      Dim strCountry As String
      Dim strCountryCode As String
    Dim FSO As New FileSystemObject
      Dim JsonTS As TextStream
      Set JsonTS = FSO.OpenTextFile(varData, ForReading)
      jsonText = JsonTS.ReadAll
      JsonTS.Close
         Set xlsWb = ThisWorkbook
      Set xlsSheet = xlsWb.Sheets(“Bank BIC Codes”)
      xlsSheet.Activate
       
      Set jsonObject = JsonConverter.ParseJson(jsonText)

      i = 2
      For Each item In jsonObject –>> Error 424 Object required
        xlsSheet.Cells(i, 1).Value = item(“country”)
        xlsSheet.Cells(i, 2).Value = item(“country-code”)
        xlsSheet.Cells(i, 3).Value = item(“list”)(“bank”)
        xlsSheet.Cells(i, 4).Value = item(“list”)(“city”)
        xlsSheet.Cells(i, 5).Value = item(“branch”)
        xlsSheet.Cells(i, 6).Value = item(“swift_code”)
        i = i + 1
      Next

    Reply

  155. Hi, Great Post.
    I need help build two nested structures. Can you help, please!?

    {“address_objects”: [{“ipv4”: {“name”: “Test 1”,“zone”: “LAN”,“host”: {“ip”: “192.168.168.10”}}},{“ipv4”: {“name”: “Test 2”,“zone”: “LAN”,“host”: {“ip”: “192.168.168.20”}}},{“ipv4”: {“name”: “Test 3”,“zone”: “LAN”,“host”: {“ip”: “192.168.168.30”

    Reply

  156. Hi Ranjith,
    thank you for perfectly structured and distinctive post. It helped me much with my JSON export which I have firstly generated directly from sql. But it had not the desired (pretty nastily nested) structure. So I tried to generate it from excel VBA. Your post helped me much with understanding the principles of generating jsons in VBA, I have managed to get the desired structure even with all the exceptions. So I don’t want anything from you but I want to thank you.
    Vera

    Reply

  157. Hi, great info. I’m having an issue with a json page that seems to use a date as one of the keys, and the date changes. How can I make this work. See part of json data below.
    Example web page >>> https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=IBM&apikey=demo

    {
    “Meta Data”: {
    “1. Information”: “Monthly Adjusted Prices and Volumes”,
    “2. Symbol”: “IBM”,
    “3. Last Refreshed”: “2021-08-20”,
    “4. Time Zone”: “US/Eastern”
    },
    “Monthly Adjusted Time Series”: {
    “2021-08-20”: {
    “1. open”: “141.4500”,
    “2. high”: “144.7000”,
    “3. low”: “137.2100”,
    “4. close”: “139.1100”,
    “5. adjusted close”: “139.1100”,
    “6. volume”: “51079829”,
    “7. dividend amount”: “1.6400”
    },
    “2021-07-30”: {
    “1. open”: “146.9600”,
    “2. high”: “147.5000”,
    “3. low”: “136.2089”,
    “4. close”: “140.9600”,
    “5. adjusted close”: “139.3422”,
    “6. volume”: “110625907”,
    “7. dividend amount”: “0.0000”

    Reply

  159. hi,
    great article. Could you please peek at the pictrure? I have a problem to target the required format. I modified separators etc. but still can’t get to the target format. Could you please help me?

    Reply

  160. I really doubt it but I hope I can still get a response. I looked below and thought I fixed my issue but i keep getting a type error. below is the code i am using to get the headers with a red arrow. if i get 2+ bookings returned on my post request then It’s within the[] as a new set of {} e.g. bookings : [{booking1},{booking2}] Why do i keep getting a type mismatch? I tried adding another bracket with (1). I understand i will need to make a for loop which i will need to try and make next.
    Set JSON = JsonConverter.ParseJson(apiWaxLRS)
    i = 2
    For Each Item In JSON
    Sheets(2).Cells(i, 1).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“duration”)
    Sheets(2).Cells(i, 2).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“location”)(“address”)(“address1”)
    Sheets(2).Cells(i, 3).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“location”)(“address”)(“postal”)
    Sheets(2).Cells(i, 4).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“location”)(“address”)(“city”)
    Sheets(2).Cells(i, 5).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“location”)(“address”)(“region”)
    Sheets(2).Cells(i, 6).Value = Item(“data”)(“bookings”)(“bookings”)(1)(“bookedTime”)
    i = i + 1
    Next

    Reply

    1. Hi, It’s a very old article. I need to dig through the code. It also depends on the text that you are passing to parseJSON.

      Reply

  162. myfile = Application.ActiveWorkbook.Path & “\data.json” must be shown as myfile = Application.ActiveWorkbook.Path & “\data.json”

    & gives error in excel vba. So, please correct it.

    Reply

  163. Hello. I am using what was demonstrated in the text below.
    And I need to include an array, inside an item that is part of an array.

    Reply

  164. Public Sub exceljson()
    Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String
    myurl = “https://staging.freightbro.com/api/agent”
    xmlhttp.Open “GET”, myurl, False
    xmlhttp.setRequestHeader “apikey”, “dyusakewjrf3712937461@44$bfdfbGD”
    xmlhttp.send
    MsgBox (xmlhttp.responseText)
    Dim json As Object, i As Integer
    Set json = JsonConverter.ParseJson(xmlhttp.responseText)
    i = 2
    For Each item In json
    MasterData.Cells(i, 1).Value = item(“agent_id”)
    MasterData.Cells(i, 2).Value = item(“agent_name”)
    MasterData.Cells(i, 3).Value = item(“vendor_id”)
    i = i + 1
    Next
    MsgBox (“complete”)
    End Sub
    I will try this code but output not generated please advise.

    Reply

  165. Hi,
    I have some information in JSON format, it looke like:
    {“data”:[{“id”:”7ad9d18f-908a-4893-8c9e-0d59de8013b9″,”number”:”9″,”tenantId”:”05f55fbc-0eeb-11ec-b60b-0638767d04b5″,”isDraft”:false,”personNames”:[{“firstName”:”Oliver”,”lastName”:”Dybdal”}],”personIds”:[{“typeOfId”:”NationalID”,”id”:”04014402468″}],”dateOfBirth”:”1944-01-04″,”emails”:[{“type”:”Private”,”address”:”[email protected]”}],”address”:{“streetName”:”Belsetsvingen 50″,”cityName”:”Rykkinn”,”zipCode”:”1348″,”countryCode”:”NO”},”salaryPaymentMethod”:{“paymentType”:”bank”,”internationalBankAccount”:false,”localBankAccount”:”26059311394″},”taxInformation”:{“mainEmployer”:true},”printPayslip”:false,”externalIds”:[{“key”:”ExternalTenantId”,”value”:”3485756″},{“key”:”LegacyEmployeeId”,”value”:”9fd8bbc9-16b7-44a4-8f85-025e99cae4f1″}],”lastChange”:”2023-01-03T06:37:29.707Z”,”previousPeriods”:[{“startDate”:”2017-01-01″,”endDate”:”2021-12-31″}]},{“id”:”22e68b8a-e21c-4bfb-8500-22de161fa052″,”number”:”10″,”tenantId”:”05f55fbc-0eeb-11ec-b60b-0638767d04b5″,”isDraft”:false,”personNames”:[{“firstName”:”Filip”,”lastName”:”Ekeberg”}],”personIds”:[{“typeOfId”:”NationalID”,”id”:”04015000320″}],”dateOfBirth”:”1950-01-04″,”emails”:[{“type”:”Private”,”address”:”[email protected]”}],”address”:{“streetName”:”Belsetsvingen 51″,”cityName”:”Rykkinn”,”zipCode”:”1348″,”countryCode”:”NO”},”salaryPaymentMethod”:{“paymentType”:”bank”,”internationalBankAccount”:false,”localBankAccount”:”26021714762″},”taxInformation”:{“mainEmployer”:true},”pensionCompanyIds”:[{“value”:”6/3485756″,”activeStart”:”2021-12-01″}],”printPayslip”:false,”externalIds”:[{“key”:”ExternalTenantId”,”value”:”3485756″},{“key”:”LegacyEmployeeId”,”value”:”49858a61-8291-4261-9efc-ec2db00ff7ff”}],”lastChange”:”2023-01-03T06:37:29.707Z”,”previousPeriods”:[{“startDate”:”2017-01-01″}]}]}
    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? I have seen that this problem probably has been solved in the comments felt earlier, but I am not able not get the code that was presented there. Really apriciated for your help.

    Reply

Leave a Reply

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