Convert Excel data to JSON format using VBA

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
%d bloggers like this: