Import Data from JSON File with Excel VBA

VBA Code: Import Data from JSON File

Sub ImportJSONData()
    ' Declare necessary variables
    Dim JSONFilePath As String
    Dim FileContent As String
    Dim JSON As Object
    Dim i As Integer
    Dim ws As Worksheet
    Dim jsonObject As Object
    Dim key As Variant  
    ' Define the path of the JSON file
    JSONFilePath = Application.GetOpenFilename("JSON Files (*.json), *.json", , "Select JSON File")   
    ' Check if a file was selected
    If JSONFilePath = "False" Then
        MsgBox "No file selected. Exiting sub."
        Exit Sub
    End If
    ' Open the JSON file and read its contents into a string
    Open JSONFilePath For Input As #1
    FileContent = Input$(LOF(1), 1)
    Close #1   
    ' Parse the JSON content using VBA-JSON (need to include JSON library)
    Set JSON = JsonConverter.ParseJson(FileContent)   
    ' Set the target worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "ImportedJSONData"   
    ' Assuming JSON data is an array of objects
    i = 1 ' Start writing data from the first row   
    ' Loop through the JSON array (assuming JSON structure is an array of objects)
    For Each jsonObject In JSON       
        ' Loop through each key-value pair in the JSON object
        For Each key In jsonObject.Keys
            ' Write the key (column header) in the first row
            If i = 1 Then
                ws.Cells(1, jsonObject.Keys.IndexOf(key) + 1).Value = key
            End If           
            ' Write the value in the corresponding row and column
            ws.Cells(i + 1, jsonObject.Keys.IndexOf(key) + 1).Value = jsonObject(key)
        Next key       
        i = i + 1 ' Move to the next row
    Next jsonObject   
    MsgBox "Data import completed successfully!"   
End Sub

Explanation:

This code is an Excel VBA macro designed to import data from a JSON file into an Excel worksheet. Here’s a breakdown of the key components:

1. File Selection Dialog

JSONFilePath = Application.GetOpenFilename("JSON Files (*.json), *.json", , "Select JSON File")

• This line opens a file dialog that allows the user to select the JSON file they want to import. It filters the file types to only show .json files.
• Application.GetOpenFilename returns the path of the selected file, or False if no file is selected. If no file is selected, the code stops executing and displays a message.

2. Reading the JSON File

Open JSONFilePath For Input As #1
FileContent = Input$(LOF(1), 1)
Close #1

• This block opens the selected JSON file for reading (Open JSONFilePath For Input As #1).
• Input$(LOF(1), 1) reads the entire file’s content into the FileContent variable. LOF(1) gives the file’s length in bytes.
• Finally, Close #1 closes the file after reading its content.

3. Parsing the JSON Content

Set JSON = JsonConverter.ParseJson(FileContent)

• This line uses a JSON parsing library (JsonConverter.ParseJson) to convert the string content of the JSON file into a usable VBA object.
• Note: For this to work, you must have the VBA-JSON library imported into your project.

4. Creating a New Worksheet

Set ws = ThisWorkbook.Sheets.Add
ws.Name = "ImportedJSONData"

• A new worksheet is created within the current workbook to store the imported data.
• The name of the sheet is set to « ImportedJSONData », but you can modify it as needed.

5. Looping through the JSON Data

For Each jsonObject In JSON

• The code assumes that the JSON file contains an array of objects. This loop goes through each object in the array.

6. Processing Each JSON Object

For Each key In jsonObject.Keys

• For each object in the array, the code iterates through its key-value pairs.
• The keys represent the field names (column headers in Excel), and the values are the corresponding data.

7. Writing Data to the Worksheet

If i = 1 Then
    ws.Cells(1, jsonObject.Keys.IndexOf(key) + 1).Value = key
End If
ws.Cells(i + 1, jsonObject.Keys.IndexOf(key) + 1).Value = jsonObject(key)

• In the first iteration, the code writes the key (field name) into the first row as column headers.
• For subsequent iterations, it writes the values into the corresponding cells under the correct columns.

8. Finalizing the Process

MsgBox "Data import completed successfully!"

• After all the data is imported, a message box is displayed to inform the user that the process is complete.

Requirements:

To use this code, you need to include a JSON parsing library for VBA, such as the VBA-JSON library. Here are the steps:

1. Download the library from the VBA-JSON GitHub repository.
2. Add the .bas file (e.g., JsonConverter.bas) to your VBA project:
   • In the VBA editor, go to File > Import File… and select the JsonConverter.bas file you downloaded.

Notes:

• This code assumes the JSON file contains an array of objects (like a list of records).
• If the structure of your JSON file is different, you may need to adjust the parsing logic accordingly.
• You can customize the code to handle nested JSON objects or arrays if necessary.
• Make sure the JSON file is well-formed, or the parser will throw errors.