Software Lab Excel To JSON
Description:
This set of VBA codes creates a customized Excel Function that can be used to convert an Excel data Table/List into JSON format. The JSON format can then be used for web purposes as this is in JavaScript format which is the main scripting language for websites.
Code:
Public Function ToJson(DataTable As Range) As String

    Dim Headerrange As Range
    Dim colcount As Long
    Dim i As Long
    Dim c As Long
    Dim textline As String
    Dim Rng As Range
    Dim quote As String
    
    Set Headerrange = Range(DataTable.Rows(1).Address)
    colcount = Headerrange.Columns.Count
    quote = """"
    
    MsgBox "Number of rows = " & DataTable.Rows.Count, vbOKOnly, "Bliss Logic"
    MsgBox "Number of columns = " & colcount, vbOKOnly, "Bliss Logic"
    
    With DataTable
    
        For i = 1 To DataTable.Rows.Count
        
        If i > 1 Then
        
            textline = "{"
    
            For c = 1 To colcount
                
                textline = textline & quote & .Cells(1, c) & quote & ":"
                textline = textline & quote & .Cells(i, c) & quote
                textline = textline & ","
            
            Next c
            
            ToJson = ToJson & Left(textline, Len(textline) - 1) & "},"
        
        End If
        
        Next i
    
            
    End With
    
    ToJson = "[" & Left(ToJson, Len(ToJson) - 1) & "]"
    
    
End Function


Procedures:
  1. Insert a new module (VBA) in your excel file.
  2. Copy and paste the above codes into the module.
  3. Type in a cell “=ToJson(Range)” where Range is your Excel List with Header Columns included.
  4. This is very similar to you using the SUM function in Excel – “=SUM(“A1:A15″)”