• Parse JSON – Converter in Excel

    Does it sound like Jason Bourne? May be. But this is not about film. It is a file format. Get JSON converter Excel VBA Code & sample JSON file. (If you are looking for Python JSON Parser click here)

    Using this code, will read data in JSON & parse the details to Excel , Txt or CSV file.

    Also Read: Create Keyboard Shortcut - to VBA Macro code - in Excel

    Parser – JSON To Excel – VBA Macro Code

    Create a new Excel workbook & save it with .xlsm format. Press Alt + F11 to view the VB editor for Excel. Then follow these preliminary steps.

    Important steps to use this JSON Parser:

    1. Got this page, download the JsonConverter.bas & include it in your VBA project.
    2. In VB editor, goto Tool->References & include “Microsoft Scripting Runtime”

    Now, copy paste this code to your VBA Project into a new module.

    We are going to use the ParseJson module that is present in the above .Bas file. The parsed data will be moved to a dictionary data type variable. To enable this dictionary data type option, we need the Microsoft Scripting Runtime option added in references.

    the JsonConverter.bas is a miracle module that works like a charm and free to use for personal use. Please read the license details & sample code available in that page to know more about it.

    But, if you are looking to parse any simple JSON file, then this code will work just fine.

    JSON Sample file

    If you don’t have a JSON file, use this sample file for testing the above code.

    If you are very much new to this file format, get few technical aspects of it from this page. This file formatting is gaining more popular. In websites, in older days xml file was more widely used. But nowadays JSON is getting used more than any CSV, XML file formats.

    Rather than using Excel to parse a JSON file, it is widely used in programming languages like PHP & Python. Because these are the programming languages that widely support Web technologies.

    This code can handle nested objects with JSON as well.

    Previous Post:
    Next Post:

    • Sunil

      Dear Sir, Thank you for your article. I’ve copied VBscript in to VB in excel develper and Json.txt file in to D drive. then added Microsoft Scripting Runtime in tools. Still it is not working

      • Dear Sunil, Please copy the JsonConverter.bas as mentioned in the step1 of Important notes.
        Regarding your email for getting custom code to convert gour JSON: please contact in Fiverr.com My Id: kumarapush777 for customized Appd. My team will provide you a feasible quote.
        Thanks much for your interest.

        • Sunil

          Thanks for your reply…