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 web-service we’ll use to get data in JSON format for this example is Datamuse.

Datamuse is a word-finding query engine. It lets you retrieve a list of words based on a variety of criteria including meaning, spelling, sound and vocabulary.

For example, you could retrieve a list of words related to duck that start with the letter ‘b’, or adjectives that are often used to describe ocean.

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.

Marcus

Marcus has been an Excel & VBA developer for over 20 years working in the trenches in Investment Banks and Assets Managers, previously in Melbourne and now in London.

  • Alberto says:

    Hi Marcus, I believe that getitemcount has not been defined…

    • Marcus says:

      Hey Alberto,
      Good pick-up – you’re absolutely right – I forgot to include the code listing for the GetItemCount helper function.
      I’ve added that in with a quick explanation.
      All the best,
      Marcus

  • Frank says:

    I was really enjoying this post, but then at the end it seems some code is missing. All the code is missing after “What’s in the JSON?” Is there a way to get that back?

    Other than the missing code, I love how you divide and explain each portion of the code you have. This makes it so much easier to learn than grabbing some code and only getting some comments to interpret!

    • Marcus says:

      Hey Frank,
      Thanks for dropping by.

      You’re right, the listing for Test_GetResponse went AWOL!
      I’ve added it back in although Thrive Architect (which is usually awesome) doesn’t seem to rendering images correctly when I edit the post.

      Thanks for the feedback about how I split & explain the portions of code. I’ll keep using that approach with future posts.

      All the best,
      Marcus

  • Gopal Mallick says:

    VERY NICE

  • Robert says:

    Is it just me or is all of the code presented as images? It’s not a lot to retype, but would be nice if I could just cut and paste it.
    Thanks!

    • Marcus says:

      Hey Robert,

      You’re absolutely right – they are images.

      I couldn’t find a WordPress plugin which would let me
      format, colour, highlight *and* ‘selectively’ number the VBA code so I could walk you through it.

      The next best option was to format, highlight and number the VBA code in PowerPoint and then save it as a image.

      In my defense, having to type code – rather than copy-and-paste – really does help to learn it 🙂

      What I will do is make the whole working spreadsheet available as a download.

      Thanks for dropping by,
      Marcus

  • Phil Griffin says:

    Hi Marcus. Great post thanks.
    I’m getting a “class not registered” error on line 4 of ParseJson (“Set script = New…”). All the references are the same as yours. I’ve checked and checked and my code is identical to yours. Any suggestions? A Google search hasn’t helped.

    • Marcus says:

      Hey Phil,

      Thanks for dropping by My Excel Genius.

      Provided you’ve got a reference set to the Microsoft XML library and Microsoft Script Control, your VBA code should compile and run.

      As a sanity check, I’ve emailed you a workbook with the code that happily runs as you’d expect. It runs on my Windows 10 / Excel 2016 laptop.

      The VBA code is accessible so you’re free to play around with it.

      Give it a try so we can try to narrow down the issue. For example; is it a code problem or a PC configuration problem. Perhaps your Script Control is corrupt (unlikely, but it’s good to eliminate the options).

      All the best,
      Marcus

      • jean says:

        got same issue. It looks like the scriptcontrol COM object was not ported to 64 bits… dead end ?

        • Marcus says:

          Hey Jean,

          No, it’s not a dead-end if you are using the 64-bit edition of Excel.

          I’m using the 64-bit edition of Windows 10 with the 32-bit version of Excel. The code example in the post works without issue.

          However, the script control is a 32-bit library and doesn’t play nicely with the 64-bit edition of Excel.

          I’ve found a work-around for 64-bit versions of Excel which I have emailed to you.

          I’ll update this post shortly to include this fix and provide a fully documented workbook everyone can download.

          All the best,
          Marcus

  • Alex says:

    When I run this I get Run-time error 438 “Object doesn’t support this property or method” on the For Each statement in the GetItemCount helper. Any idea what might be causing this

    thank you for any help!

    • Marcus says:

      Hey Alex,

      Just prior to calling the GetItemCount helper function, there’s this line:

      Set items = script.Eval(“(” & response & “)”)

      This takes the raw JSON which was returned from the website and turns it into an “object” which can be iterated through like a dictionary or collection.

      The first thing to check is the raw JSON that has been returned from the website. Is it incomplete or corrupt for example.

      Try adding the following line right after the script.Eval line above:

      Debug.Print response

      This will spit out the raw JSON to the Immediate window so you can check it.

      If there is no JSON or it looks corrupted, if you’re running this code from work (corporate) environment, make sure there are no firewall issues stopping you from retrieving data from the web.

      If it looks okay, then check what’s in the “items” object.

      If you step through the code, you should be able to view the contents of the items object. It should resemble an array.

      Let me know how you go with those 2 checks.

      Another commenter, Phil experienced issues as he’s running a 64-bit edition of Excel (I’m running a 32-bit edition).

      I’ll probably update this post with your and his feedback as it’s been really helpful to know what things can go wrong.

      Thanks for dropping by,
      Marcus

  • >