Parse JSON Using VSTO For Excel

17 Jan Parse JSON Using VSTO For Excel

Howdee Excel Ninjas! My article on using VBA to import JSON into Excel has been one of my most popular. As a follow up to this I wanted to demonstrate how to Parse JSON using VSTO (Visual Studio Tools for Office) add-in to help perform similar actions. More and more, I use this approach instead of using the VBA-JSON library I covered in my previous article. To get started with this, you’ll need to download Visual Studio (I’m using the 2017 Community Edition) and you should be familiar with starting a VSTO project. If you’re not, I have an article that will quickly walk you through the basics here. Before we dive into this project, I want to explain why I’m moving away from an approach that is so popular.

Side-note: This is going to be a very basic introduction to the topic and I’ve tried to keep the code as simple as possible for those of you new to C#. If you’d like me to do get a little more advanced in my demonstrations, let me know and I’d be happy to do an additional write up.

Why VSTO vs. VBA-JSON?

There are a handful of reasons to choose the VSTO route in my opinion. The JSON library we will be using, Newtonsoft, is one of the most popular libraries in the world, not just limited to JSON libraries. The support is very good and I have yet to find a single bug in my use of it. The same cannot be said for VBA-JSON. There are a couple of bugs that make it impossible to parse some JSON objects which can be frustrating at best.

Visual Studio has a neat feature that allows you to turn a JSON object into a class just by pasting data in (we will cover this in depth later). This is extremely helpful when creating classes so you can quickly deserialize a JSON object to that class and manipulate it from there. To me, accomplishing this task in the VBA library can take two or three times as long and is infinitely more frustrating at times due to the lack of intellisense.

Lastly, the community support is a huge reason. Particularly if you’re using C#, there are thousands of developers out there that can answer your questions on stack overflow as well as countless blogs like this where you can find the information. If you’ve only been developing in VBA and are nervous about transitioning to a language like C#, don’t be. If you’ve got a decent understanding of object oriented programming, you’ll pick it up in no time.

With my rambling out of the way, let’s learn how to Parse JSON using VSTO!

Creating a VSTO project to parse JSON

To get started, open Visual Studio and create a new VSTO Excel add-in. I would recommend steering clear of the web add-in for now unless you’re familiar with Visual Studio and JavaScript. Once you’re project is created, you should see two methods in the ThisAddIn class. Leave those alone, but the two methods you see on the left. They are simple methods that return the active sheet and the active cell.

Make sure that these methods are public, so you can access them from other areas of the project. Next, we need to crate our ribbon. For this example, I’m going to keep the interface very simple and just add a “Parse JSON” button by dragging the button tool from the toolbox to the designer as shown to the right.

Next, double click the button you created to create a method that is bound to the “Button_Click” event. The code we put in this method will fire whenever the button is clicked. For the sake of this example, we will use a Resource object to store our JSON object (here is a good Stack Overflow post on a couple of ways to do this). This object was generated using https://next.json-generator.com which is a great resource to use when you’re building and testing methods to parse JSON. I won’t show the string here as it is fairly lengthy. I intentionally made it lengthy to demonstrate a cool feature the next step.

Paste class as JSON

This is one of the main reasons why I push people to Parse JSON using VSTO. You have the ability to paste an example of your data into Visual Studio and the class to which you can deserialize the JSON string is generated for you. I always found this method tedious and frustrating in VBA. To do this, create a new class by right-clicking on your project and selecting “Add”, then “Class”.

In the popup that follows, name the class something that describes what the object you’re going to be serializing is. For this, I’m just using ExampleJson. Before moving on, make sure that the class is preceded by the public keyword so that it’s visible to other sections of your project.

Next, make sure you have a example string of your JSON copied to the clipboard. Click inside the class curly braces, then click “Edit” from the ribbon menu in the top left and select “Paste Special”, then click “Paste JSON As Classes” as shown below. This will immediately evaluate your JSON and create the structure you need so you can deserialize any subsequent JSON strings to this class. Next, let’s learn how to deserialize our JSON.

Parse JSON using VSTO and Import to Excel

Before we Parse JSON using VSTO, we need to import the Newtonsoft library we will use to parse our JSON. A couple of ways you can do this. You can open the package manager console by clicking on the Tools tab on the toolbar and then Selecting “NuGet Package Manager” and you can select either the “Package Manager Console” or right-clicking on the project in the solution explorer and selecting “Manage NuGet Packages…” as shown below.

In the package manager console, typing “Install-Package Newtonsoft.Json” will install the latest version of Newtonsoft.Json into your project. If you chose to use the NuGet Package Manager, you can search for Newtonsoft.Json and install it from there easily as well. Last step before we start writing code is to make sure we include the necessary namespaces that we will need to accomplish our goal. To do this, we need to add the following using statements to the top of the file (“using System.Collections.Generic”, using Microsoft.Office.Tools.Ribbon”, and “using Newtonsoft.Json”). This allows us to use lists and the Newtonsoft library.

With that done, we now have everything we need to Parse JSON using VSTO and import into Excel! The method to parse our JSON file and place the data in Excel looks like this:

As mentioned earlier, this is a pretty basic example, but the intent is to show how easy it is to Parse JSON using VSTO to an object. The second line in the method parses the string and converts it to an object whose structure matches the class we created earlier. It’s now very easy, as shown with the data object, to do what we want with this data. I would recommend create an additional class that handles converting the parsed JSON to a data table that you can then serve up to Excel. Now, we can run the application and click the button on our ribbon and the JSON will be pasted into the range wherever our cursor is clicked!

As always, this file is available for download if you’re a free subscriber to the site in the Example Files section. As is the case with my VSTO examples, it is a zipped folder, so you’ll need to unzip and open in visual studio to run.

Does this approach to working with data appeal to you? Let me know either via email or in the comments below.

Cheers,

R