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

          • Douglas says:

            Marcus,
            I’m having the same challenge as Phil and Jean, having 64-bit version (work). And I’m not seeing this fix you mention. I’m created parsing VBA for that works, but with 200K lines of data takes a lot of time to populate spreadsheet and downloading is a lot quicker. Would like to see if this speeds it up. Thanks, Douglas

          • Michael says:

            Hi Marcus,

            Great code and it does exactly what I need. btw I thought having the code as an image and forcing us to write it out was a subtle learning aid.

            Any chance I could also get a copy of the 64 bit workaround?

            Cheers,

            Michael

          • Steve says:

            I too am also having issues with 64 bit here. Any luck with getting this to work? I would be very interested in seeing this code for 64 bit.

          • Marcus says:

            Hello Steve,
            Thanks for dropping by (and for your email too).

            As mentioned, I have found a workaround for this issue (64 bit Excel) and I’m just giving it a test.

            I should have the blog post updated this week with a free downloadable workbook for everyone to try out.

            All the best,
            Marcus

          • Marshall says:

            Would really appreciate getting that workaround. You’re article is great — until testing ParseJson() failed. I presume it’s because my Excel is 64-bit, and I haven’t found a solution to that.

            If you’re not ready to post the workaround publicly yet, I’d be happy to test it for you if you e-mail it to me as you have to some others. I’ll report back with the results.

            Thank you.

          • Marcus says:

            Hey Michael,

            Glad to hear the code’s achieving what you need to do.

            I’ve emailed you with a copy of the test workbook that (should!) run 32 and 64 versions of Excel. I’ll update the post and publish the workbook after some more testing.

            All the best,
            Marcus

          • Henry says:

            i would also like the workbook for the excel 64 bit since i am also getting the error message

            thanks in advance

          • Robert says:

            Hi Marcus,
            Great VBA but unfortunately I get the same problem of ‘class not registered ‘ – running 64bit windows and 64 bit excel at the line Set script=New MSScriptcontrol.scriptcontrol.
            If you have a workaround that would be great to see
            Robert

  • 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

  • Johnberne says:

    Interesting article, thank you for sharing it. It’s a good approach for Excel and json conversion. I have found also this solution on the internet https://dev-bay.com/json-to-excel/ which also works good – but here one question about that, is it a good approach to flatter JSON like this tool does?

    • Marcus says:

      Hey Johnberne,

      Thanks for dropping by. I’m glad to hear you liked the article.

      The tool you provided the link for looks quite interesting. IN a sense, that’s sort of what we’re doing with the VBA code example in this article.

      Now whether it’s a good approach to flatten JSON like that tool does, has a very succinct answer: it depends.
      The JSON example in this article is very simple. It’s a two-dimensional structure and very easy to ‘flatten’.

      However, JSON can also represent far more complex data structures. At one bank I did some work for, financial products were represented in JSON. These can become quite complex with embedded data-within-data. That is, the data can be hierarchical. In these cases it may not make sense to flatten the JSON.

      The other aspect to consider is what you plan to do with the flattened JSON data. One benefit of structured data like JSON and XML is that it *is* structured, so that relationships and hierarchies can be represented in the JSON (or XML). This may be more difficult to represent in flattened CSV or Excel files.

      I hope this helped.

      All the best,
      Marcus

  • Justin Small says:

    Hi wondering if you can help using latest Excel 64bit version, keep getting a VBA break in ParseJson function on line Set script = New MSScriptControl.ScriptControl with run time error “Class not registered” Any ideas on how to fix?

  • Emmanuel says:

    Hello Marcus, Thanks for the sharing.
    I have to face with some troubles.
    On the line
    Set Script = New MSScriptControl.ScriptControl
    the executer replied “error 429 A compound ActiveX can not create an Object”.

  • siva Kumar says:

    Thanks.

    How to fetch values from nested json using excel vba.

    Please share sample vba having the code mentioned above

  • Cecile Van Niekerk says:

    Hi Marcus,
    Thank you for this very interesting article! The script control solution is brilliant! I tried to use the JsonConverter code before but I find it slow when working with large data sets.

    Do you perhaps know how to populate an excel sheet when you have n number of key and value pairs in the JSON results and the key names are not known beforehand? (where you are referring to itm.word)

    Thank you in advance,
    Cecile

  • Abhijeet says:

    hello sir
    great post
    sir kindly show the workaround 64bit

  • john says:

    Marcus not sure if this thread is still alive. But I’m not seeing the fix everyone is asking for for your above code.
    Class not registered error. please advise….

  • Stephen D says:

    Hi Marcus,
    What a great article. Did you ever post the 64 bit workaround?
    Thanks

  • Darren says:

    Hi, Thanks for this its brilliant, worked fine playing around, trying it on my home machine and didn’t realise office 365 is 64bit!

    Any updates to the article at alll?

    Many Thanks 🙂

  • >