Convert Excel data to JSON format using VBA

January 23, 2017January 9, 2019 Don't You Dare Convert to JSON, Excel VBA, JSON

JSON format data is widely popular when it comes to send and receive information between a web server and a client. I bumped into a situation where I needed to do the POST to my server and obviously the data format I needed was in JSON. So, I wrote this little Excel VBA macro to convert a given range into a JSON format.

Excel Macro to convert given range to JSON format

Public Function ToJSON(rng As range) As String ‘ Make sure there are two columns in the range If rng.Columns.Count < 2 Then ToJSON = CVErr(xlErrNA) Exit Function End If Dim dataLoop, headerLoop As Long ' Get the first row of the range as a header range Dim headerRange As range: Set headerRange = range(rng.Rows(1).Address) ' We need to know how many columns are there Dim colCount As Long: colCount = headerRange.Columns.Count Dim json As String: json = "[" For dataLoop = 1 To rng.Rows.Count ' Skip the first row as it's been used as a header If dataLoop > 1 Then ‘ Start data row Dim rowJson As String: rowJson = “{” ‘ Loop through each column and combine with the header For headerLoop = 1 To colCount rowJson = rowJson & “””” & headerRange.Value2(1, headerLoop) & “””” & “:” rowJson = rowJson & “””” & rng.Value2(dataLoop, headerLoop) & “””” rowJson = rowJson & “,” Next headerLoop ‘ Strip out the last comma rowJson = Left(rowJson, Len(rowJson) – 1) ‘ End data row json = json & rowJson & “},” End If Next ‘ Strip out the last comma json = Left(json, Len(json) – 1) json = json & “]” ToJSON = json End Function

Collections

Convert Excel data to JSON format using VBA

Excel Macro to convert given range to JSON format

Like this:

Related

Excel Macro to convert given range to JSON format

Share this:

Like this:

Related

You May Also Like

Where is Microsoft Excel VBA or Excel IDE

Convert Excel data to JSON CSV and XML format

Create Dictionary and KeyValuePair Classes in Excel VBA