One increasingly common task for Excel users is to retrieve data from the internet.

Simply, retrieving data from the web is the first challenge.

This sounds simple enough (and as you’ll soon see, it is).

But most people run into a roadblock with the second challenge: wrestling with data in JSON format.

Read on to discover the easiest and fastest method to get JSON data into Excel...

How to Import & Parse JSON Data with VBA

You’ve got several approaches to parsing data from the web which is in JSON format:

1) Roll Your Own

You could write your own VBA code to parse the JSON data.

This approach is only recommended if you have acute masochistic tendencies.

This path (of parsing JSON) has been travelled by others before you.
Leverage off their work 😉

2) Free / Open-Source Libraries

You can use free or open-source VBA libraries like VB-JSON or VBA-JSON.

While many of these libraries do work, some do have their own issues, may not be supported and often suffer from performance issues.

3) Third Party Libraries

You could buy a parsing library from a third-party provider.

But if you’re in a corporate environment, you may find it difficult to purchase non-standard software products.

In one recent project, it took 93 days from request to authorisation to installation to get my favourite VBA productivity suite, MZ-Tools installed.

Good thing I wasn’t in a hurry.

But if you are . . .

4) Standard COM components

The last approach, which we’ll cover here, is using standard COM components.

At the end of the day, Excel is just one big component.

What this means is that we can leverage other COM components and libraries quickly and (usually) easily.

Oh, and did I mention they're free 🙂

What the heck is JSON?

Before we get cracking, let’s give a little attention to JSON.

You’re probably already familiar with XML.

Just like HTML uses tags to structure a webpage, XML uses tags to structure data. This data can be saved to a text file or transmitted over the internet.

JSON, which stands for JavaScript Object Notation, is another method of structuring data, although it doesn’t use tags.

Originally designed for JavaScript, JSON is now used widely for transmitting data between apps and across the web.

You can read all the gory details here.

Using a Web-service to Get Data

The Magic of COM Libraries

You’ll be using two COM libraries to do all the heavy lifting for you.

The first is the Microsoft XML parser library which also includes the ability to query a website and retrieve data.

The second is the Microsoft Script Control

Retrieving the Web Data

The first thing you’ll need to do is set a reference to the Microsoft XML parser library in the Excel Visual Basic Editor (VBE):

You should find the Microsoft XML library in the list of References.

Its exact location may differ on your computer.

Once you’ve done that, in a new module, enter the following VBA function.

You can see it accepts a web URL as an input parameter and will return some text (as a string).

So, what’s it doing?

1. The first couple of lines declare some constants to make the code easier to read and avoid including any “magic numbers”.
2. You then declare a variable as the Microsoft XML parsing library and instantiate it.
3. Next, you’ll open a connection to the web-service with the URL, request that the data come back in JSON format and send the request.
4. Now we wait. We’ll just staying in holding pattern until the request (declared as a Microsoft XML object) tells us it’s finished.
5. The function finally returns the JSON it retrieved from the web-service.

Testing Your Code

Let’s test it out.

Enter this routine above the GetResponse() function.

Remember, we’re using the Datamuse web-service to test retrieving JSON data. This query says:

Give me a list of adjectives used to describe the word ‘ocean’.

Let’s test it out.

Enter the following test routine above the GetResponse() function.

Remember, we’re using the Datamuse web-service to test retrieving JSON data.

This query says: Give me a list of adjectives used to describe the word ‘ocean’.

If you pasted this URL into your web browser, it’d look something like this:

Making Sense of JSON

Okay, we’re half-way there.

You've got a clean and simple way to retrieve data from a web-service.

Now you just need an efficient way to parse the JSON and load it into Excel.

Enter the Script Control . . .

Now set a reference to the Microsoft Script Control in the Excel VBE:

The script control has several other purposes, including the interpretation and execution of scripts in a host application.

But for your purposes, you just need it to parse JSON.

Enter the following function in your module:

Let's have a look at what it's doing . . .

1. First you declare your ScriptControl variable and set the Language property to "JScript", Microsoft's flavour of JavaScript. 
2. Next you declare another Object variable. You then use ScriptControl's Eval to parse the JSON into a structure you can interrogate.
3. Here we want to populate an array with the Word collection in the items object, but first we use a helper function called GetItemCount to count the number of elements in the items object to size the array correctly.
   Yes, you could use ReDim Preserve to keep resizing the array but it's slow and inefficient.
   I've listed the GetItemCount helper function below. 
4. Next you loop through each element in the items object and assign each successive value to your array.
5. Finally, just for good measure, return the number of elements found.

Now you have a simple routine to parse your JSON data, enter the specific data you want into an array and tell you how many items it found.

What’s in the JSON?

Before we look at the code in more detail, let’s take a quick peek at what’s inside that JSON object we retrieved from the web-service.

Remember, what was returned is a JSON object, not a VBA array.

However, you can peer inside the object as well as loop through it to pluck out data you’re interested in.

When the MS Script Code does is convert the blog of JSON text you saw in the browser screenshot above to something structured.

 If you looked at it in the VBE Locals window, it would look like this:

1. Notice that the structure “looks” like a regular array with a hierarchy.
2. But remember, it’s really a JavaScript object.

Okay, to be pedantic, it’s a JScript – Microsoft’s ‘version’ of JavaScript – object.

 Now, back to the code . . .

1. The first few lines declare a variable, script, as being a shiny new MS Script Control object and tells it you want to use “JScript”. The other alternative is “VBScript”.
2. You then define an object variable called items (original, I know) to hold the data structure. This is then populated by asking the Script Control to evaluate the JSON.
3. As this isn’t a true array you can’t use UBound() to determine how many items it has. Instead, you can use a helper function to loop through the Items object and count them.
   You’ll then use this number to resize the output array, ready to be populated. This also helps avoid using ReDim Preserve which can be a tad on the slow side.
4. You then loop through each top-level element in the object, retrieving the value for “word” and passing it to the VBA array.

Just a minute . . .

How did you know the element you wanted was called “word”?

It’s right there in the JSON string that you retrieved earlier.

Remember, JSON is composed of a couple of simple data structures, the main one being an unordered set of name/value pairs (essentially a dictionary).

Now that you’ve looped through the object and populated the array that was passed by reference, the function returns a count of how many items were found.

Now let’s see it in action.

Update the Test_GetResponse routine so it looks like this:

Let’s pull this code apart. . .

1. First you declare some variables you need for this process. One to hold the JSON data returned from the webservice (json), an array to hold the values parsed from the JSON object (words) and a range object (Target) so you can paste the results to a worksheet.
2. Next you call the GetResponse function, passing the URL and have the resulting JSON assigned to the json variable.
3. Finally, you call the ParseJson function, passing the JSON object and the array to be populated.

If this returns a value greater than zero, you swing into action and define where you want the list of words entered and assign the array to that range.

The eagle-eyed among you probably noticed that you’re also flipping the array using the worksheet Transpose function before assigning it to the Target range.

Meanwhile, you should now have a list of words in worksheet one.

You could have used this to populate a listbox, create a report, anything you'd normally do with extract data.

Over to You

Admittedly, the JSON data structure used in this example was not very complicated. However, the same principles apply to more complex data structures.

Take your time to example the returned data in the Locals window and you'll be in a better position to parse even the most complicated JSON data.

What challenges have you faced importing and processing JSON data into Excel. How did you solve them?

Let me know in the comments below.

If you're still wrestling with some JSON data let me know about that too.