How To Automatically Import Json Data Into Excel Using Vba - Json

JSON (Javascript Object Notation) is the about used information commutation format present. Microsoft Excel doesn't accept built-in support for importing JSON to excel or exporting excel information to JSON.

VBA-JSON is an excellent library for parsing JSON in VBA. Lets encounter how to handle JSON in Excel VBA. If you're new to JSON and then read JSON tutorial for beginners

Getting Started

Download VBA JSON latest version from hither
Extract information technology, open up VBA code editor in excel (Alt + F11) and import the library every bit shown in the gif below.
Add together a reference to Microsoft scripting runtime. (Tools > references > select)

Import JSON to Excel

This library provides a simple method ParseJson to parse JSON string into a dictionary object which tin can be used to excerpt information. Let's see an example.

I'chiliad using imitation data from http://jsonplaceholder.typicode.com/ which is an API service with simulated Json information.

Nosotros'll be pulling user information from http://jsonplaceholder.typicode.com/users by making a Become asking which responds with Json information.

Read more about GET requests in VBA hither

Next, we'll parse that Json and import it to excel. Lawmaking 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", Imitation http.Send Ready JSON = ParseJson(http.responseText) i = 2 For Each Item In JSON Sheets(1).Cells(i, ane).Value = Item("id") Sheets(ane).Cells(i, 2).Value = Item("name") Sheets(ane).Cells(i, iii).Value = Item("username") Sheets(1).Cells(i, 4).Value = Item("email") Sheets(1).Cells(i, 5).Value = Detail("address")("city") Sheets(1).Cells(i, half-dozen).Value = Item("phone") Sheets(1).Cells(i, seven).Value = Item("website") Sheets(1).Cells(i, 8).Value = Item("company")("name") i = i + 1 Adjacent MsgBox ("complete") Terminate Sub

Lawmaking explanation

First, define JSON as an object and brand a GET request to JSON API
JSON data received in the response is parsed by passing information technology into ParseJson method.
parsed data is converted into a drove of dictionaries.
Loop through the collection to go each user's details and gear up its values to the first canvas.

Running to a higher place code looks like gif beneath.

Reading JSON from a file

In the same case above, If yous want to read JSON data from a local file so you can employ FileSystemObject to read all text in the file and so pass information technology to ParseJson method.

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

Export Excel to Json

VBA-JSON provides some other method ConvertToJson which tin can be used to convert excel data into JSON. Here's an example.

Sample data with Name, Phone and Email is nowadays in second canvass. Let's convert information technology into JSON

Code for this looks similar :

Public Sub exceltojson() Dim rng Equally Range, items As New Drove, myitem As New Lexicon, i As Integer, prison cell As Variant set rng = Range("A2:A3") 'Set rng = Range(Sheets(two).Range("A2"), Sheets(2).Range("A2").End(xlDown)) utilise this for dynamic range i = 0 For Each cell In rng Debug.Print (cell.Value) myitem("proper noun") = cell.Value myitem("electronic mail") = cell.Kickoff(0, 1).Value myitem("phone") = cell.Commencement(0, 2).Value items.Add together myitem Gear up myitem = Aught i = i + 1 Side by side Sheets(1).Range("A4").Value = ConvertToJson(items, Whitespace:=2) End Sub

Code Explanation

First, define rng as range and set it to data range.
ConvertToJson method takes a dictionary collection or array as parameter. So we should pass our information as a collection.
A Dictionary is an object with keys and values just like JSON but doesn't back up multiple items like arrays or collections, so we create a dictionary for each item and push it into an array or a drove.
Define a dictionary and a collection, loop through the range and set each row'southward information into myitem
Push myitem into collection and ready it to nothing, because nosotros are using the same lexicon to add next row's information and push it to drove again.

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

Running above lawmaking looks similar gif beneath

Consign Excel to JSON file

In the same case above, If you want to consign excel data to JSON file then It can be washed past 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'southward folder.

Public Sub exceltojsonfile() Dim rng Every bit Range, items As New Drove, myitem As New Dictionary, i Equally Integer, cell Equally Variant, myfile As String Gear up rng = Range("A2:A3") 'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").End(xlDown)) utilize this for dynamic range i = 0 For Each prison cell In rng Debug.Impress (cell.Value) myitem("name") = cell.Value myitem("email") = prison 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 &amp; "\data.json" Open up myfile For Output As #i Print #1, ConvertToJson(items, Whitespace:=2) Shut #1 Finish Sub

Consign Excel to Nested JSON

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

Public Sub exceltonestedjson() Dim rng As Range, items As New Collection, myitem Equally New Dictionary, subitem As New Dictionary, i Every bit Integer, cell As Variant Set rng = Range("A2:A3") 'Set rng = Range(Sheets(2).Range("A2"), Sheets(2).Range("A2").Finish(xlDown)) use this for dynamic range i = 0 For Each cell In rng Debug.Print (cell.Value) myitem("proper noun") = cell.Value myitem("email") = cell.Offset(0, 1).Value myitem("phone") = cell.Beginning(0, 2).Value subitem("country") = jail cell.Kickoff(0, 3).Value myitem.Add together "location", subitem items.Add myitem Set up myitem = Nothing Gear up subitem = Nothing i = i + 1 Next Sheets(2).Range("A4").Value = ConvertToJson(items, Whitespace:=ii) Cease Sub

Running higher up lawmaking looks similar image below