In this article, we will learn how to extract a nested JSON value in VBA Excel using the VBA-Json Parse Nested Json package.
I will explain a step-by-step process for parsing complex JSON Data using VBA JSON in Excel VBA.
You can download the source code at the bottom of the post
Recently I’m working on a Excel VBA project in which I need to call the rest and parse the json response. I google this question and but can’t find any good article which covers this topic with complex json response. so that I decided to write an article on Parsing Nested Arrays using VBA and JSON.
VBA Excel doesn’t have a built JSON parser for us , that’s why are going to use VBA-tools JSON parser package to parse the JSON that we receive after making a rest api calll.
Go to the GitHub link : https://github.com/VBA-tools/VBA-JSON
and download code.
And open your Excel sheet and go to the developer tool and visual basic and then import the JSON converter that we download from Github.
Go to File and click on Import file and naviate to the folder that and select the JsonConverter.bas And click on Open.
So now go back to the visual basic IDE and click on Tools and select references and Microsoft scripting runtime references in our project.
So let write code for parsing json.
Simple Json Parsing Example
Private Sub ParseSimpleJson() Dim JsonObject As Object Dim strResponse As String strResponse = "{""name"": ""johny"", ""address"": { ""country"": ""USA"",""city"": ""New York City"" } }" Set JsonObject = JsonConverter.ParseJson(strResponse) MsgBox ("User name :" & JsonObject("name") & " User Country: " & JsonObject("address")("country")) End Sub
1. Parsing Nested Arrays using VBA and JSON
Sample json
{
"apidata": {
"success": true,
"data": {
"music_events": {
"Magic": {
"users": ["Tayler",
"Ally"],
"event_status": "Pending",
"event_sites": {
"ticketbet": {
"odds": {
"h2h": ["1.86",
"1.99"]
},
"last_update": 1488956952
},
"stream411": {
"odds": {
"h2h": ["1.70",
"2.10"]
},
"last_update": 1488957101
},
"darkmusic": {
"odds": {
"h2h": ["1.83",
"1.98"]
},
"last_update": 1488957104
},
"lastride": {
"odds": {
"h2h": ["1.83",
"2.00"]
},
"last_update": 1488957115
}
}
}
}
}
}
}
Our goal is to get the music_events, users details into table and the event_sites data into a separate table.
VBA Code for parsing above json
Private Sub ParseNestedJson() Dim JsonObject As Object Dim strResponse As String Dim music_events, k, users, v, event_sites strResponse = Sheet1.Range("A1").Value Set JsonObject = JsonConverter.ParseJson(strResponse) Set music_events = JsonObject("apidata")("data")("music_events") For Each k In music_events Debug.Print "event", k Set users = music_events(k)("users") For Each v In users Debug.Print , "participant", v Next v Set event_sites = music_events(k)("event_sites") For Each v In event_sites Debug.Print , "site", v Next v Next 'MsgBox ("User name :" & JsonObject("name") & " User Country: " & JsonObject("address")("country")) End Sub
2. Parsing complex JSON Data using VBA JSON
Sample Json
{
"UniqueId": "{344DSD-343-34D-343-23SDSDSD}",
"from": "2021-01-16",
"to": "2021-01-22",
"data": [
{
"date": "2021-01-16",
"person": "{34343DS-343-3434-343-SFDSS343}",
"personName": "Rohit Smith",
"company": "{SDSD344-343-343-343-3FDFDFD}",
"companyName": "Appsloveworld pvt ltd",
"minutes": "400",
"task": [
{
"name": "Training",
"code": "TRN",
"minutes": "120"
},
{
"name": "Human Resources",
"code": "HR",
"minutes": "150"
},
{
"name": "Yoga",
"code": "YG",
"minutes": "15"
},
{
"name": "Lunch",
"code": "",
"minutes": "30"
}
]
}
]
}
There may be any number of ‘data’ records, as well as any number of ‘tasks’ within each “data” including zero.
we want a row in the spreadsheet for each activity, with the task name and other data outputted next to that day’s task.
VBA Code for that
Sub NestedJsonExample() Dim ts, act Dim Json As Object, c As Range, strResponse As String 'reading json from a worksheet cell... strResponse = Sheet1.Range("B1").Value Set Json = JsonConverter.ParseJson(strResponse) Set c = ActiveSheet.Range("C5") 'loop over timesheets For Each ts In Json("data") 'loop over timesheet activities For Each act In ts("task") c.Resize(1, 11).Value = Array(Json("UniqueId"), Json("from"), Json("to"), _ ts("date"), ts("personName"), ts("companyName"), _ ts("minutes"), act("name"), act("code"), _ act("minutes")) Set c = c.Offset(1, 0) Next act Next ts End Sub
NestedJsonExample() Subroutine:
- ts and act are declared as variables to loop over timesheets and activities within each timesheet, respectively.
- Json is declared as an object to store the parsed JSON data.
- c is declared as a range to specify the starting cell for outputting the parsed data.
- strResponse is declared to hold the JSON string retrieved from cell B1 of Sheet1.
Parsing JSON:
The JSON string stored in cell B1 of Sheet1 is read and parsed using the JsonConverter.ParseJson method, and the result is stored in the Json object.
Looping Over Timesheets and Activities:
- The code iterates through each timesheet (ts) within the "data" array in the parsed JSON.
- Within each timesheet, it further loops through each activity (act) within the "task" array.
Populating Excel Cells:
- For each timesheet activity, the relevant data (such as UniqueId, date, personName, companyName, etc.) is extracted from the JSON object and populated into cells starting from cell C5 on the active sheet.
- The data is populated in a row, and the cell range is incremented vertically using c.Offset(1, 0) for each new set of data.
How to effectively extract and parse nested JSON data in VBA Excel and populate the extracted data into Excel cells for further analysis or processing.
Some information Regarding VBA
Excel VBA is the programming language of Microsoft Excel like for Microsoft Office projects like Word and PowerPoint.
VBA is the truncation for Visual Basic for Applications. It is an occasion driven programming language from Microsoft. Which is currently fundamentally utilized with Microsoft Office applications like MS-Excel, MS-Word and MS-Access. It helps in the making of tweaked applications and their answers, which improve the abilities of those applications. The benefit of this component is that we don’t have to introduce Visual Basic on our PC yet introducing Office assists us with accomplishing our target.
We can utilize VBA in all renditions of Office from MS Office 97 to MS Office 2013. You can likewise explore different avenues regarding other present day forms of Office that are accessible with them. Dominate VBA is the most famous of all VBA and the benefit of utilizing VBA is that we can assemble an amazing asset utilizing Linear Programming.
Visual Basic is a programming language that accompanies a coordinated advancement climate. Planned by Microsoft, the Visual Basic download makes coding a basic and pleasant experience. Reasonable for all clients, including fledglings and specialists, this language is object-driven and gives you admittance to sentence structure developments and an information base of components. You can fabricate a scope of Windows applications and front-end frameworks.
What is Windows Visual Basic?
Visual Basic is an article driven improvement climate and PC programming language made by Microsoft. The framework gives a graphical UI that permits them to alter the code by relocating components, permitting clients to change the appearance and conduct of the application. The article arranged language depends on BASIC and is considered appropriate for amateurs to code.
Microsoft expected to improve on the language and backing quicker coding. That is the reason it is known as RAD or Rapid Application Development System. With its assistance, coders can model applications prior to thinking of them in a more effective however troublesome dialect. What’s more, Virtual Basic likewise gives punctuation that is more clear and data set associated.