[Excel VBA] How to Parse JSON Data and Write to Cells

Introduction

When handling JSON data using only Excel VBA, using ScriptControl to parse data with JavaScript’s eval function is a convenient method.

This article explains the steps to read JSON in an array format and write the values of each object sequentially to cells.

Preparation Environment

• OS: Windows 10 / 11
• Excel: Microsoft 365 / 2019 / 2016
• Additional Libraries: None (Standard features only)

Sample Code

Sub ParseJsonWithScriptControl()
    
    Dim jsonText As String          ' JSON String
    Dim jsonArr  As Object          ' Parsed Array
    Dim itm      As Object          ' Each Object
    Dim sc       As Object          ' ScriptControl
    Dim tgtCell  As Range           ' Writing Start Cell
    
    ' Construct JSON String (Pay attention to comma delimiters)
    jsonText = "[" & _
               "{""Name"":""Mori"",""Value"":100}," & _
               "{""Name"":""Hayashi"",""Value"":200}," & _
               "{""Name"":""Ki"",""Value"":300}" & _
               "]"
    
    ' Evaluate JSON using ScriptControl
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    Set jsonArr = sc.CodeObject.eval(jsonText)
    
    ' Specify the start cell for writing
    Set tgtCell = Sheet1.Range("A2")
    
    ' Output each object to cells
    For Each itm In jsonArr
        tgtCell.Value = itm.Name
        tgtCell.Offset(0, 1).Value = CallByName(itm, "Value", VbGet)
        Set tgtCell = tgtCell.Offset(1)   ' Move to the next row
    Next itm
    
End Sub

Key Points of the Code

ScriptControl

It dynamically generates a JavaScript environment and converts the JSON string into objects using eval.

JSON String Delimiters

When listing multiple objects, be careful not to forget the commas between them.

CallByName

This function specifies property names as strings to retrieve values dynamically.

Application Ideas

• Parsing Nested JSON: Use logic like itm.Children(0) or recursive loops to navigate through deep hierarchies.
• Using with VBA-JSON: Consider using an external library (VBA-JSON) for large-scale data or when strict type conversion is required.
• Processing JSON from APIs: Call an API using MSXML2.XMLHTTP and assign the ResponseText to jsonText.

Summary

By using ScriptControl, you can easily parse JSON without additional libraries.

Expanding array-type JSON into Excel sheets makes data formatting and aggregation easier. Please choose between this method and the VBA-JSON library depending on your specific requirements.