Excel转JSON的VBA脚本问题：如何去除末尾多余逗号

使用VBA脚本将Excel数据转换为JSON时，输出的JSON末尾始终存在多余逗号，导致JSON解析报错，调整过部分代码片段但未解决，寻求修复方案。

[
  {"Name": "Alice", "Age": 30},
  {"Name": "Bob", "Age": 25},  // 此处为多余的末尾逗号
]

JSON.parse: unexpected non-whitespace character after JSON data at line 4 column 2 of the JSON data

' 原尝试修改的片段（每行数据后直接添加逗号）
For Each cell In ws.Range("A2:A" & lastRow)
    jsonStr = jsonStr & "{""Name"": """ & ws.Cells(cell.Row, 1).Value & """, ""Age"": " & ws.Cells(cell.Row, 2).Value & "},"
Next cell

Sub ExcelToJSON()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim jsonStr As String
    Dim cell As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    jsonStr = "["
    
    For Each cell In ws.Range("A2:A" & lastRow)
        jsonStr = jsonStr & "{""Name"": """ & ws.Cells(cell.Row, 1).Value & """, ""Age"": " & ws.Cells(cell.Row, 2).Value & "},"
    Next cell
    
    jsonStr = jsonStr & "]"
    
    ' 输出到文本文件
    Open "C:\output.json" For Output As #1
    Print #1, jsonStr
    Close #1
End Sub

方案1：判断循环项是否为最后一项再添加逗号 #

不再每次循环都追加逗号，仅在非最后一行的项后添加：

For Each cell In ws.Range("A2:A" & lastRow)
    jsonStr = jsonStr & "{""Name"": """ & ws.Cells(cell.Row, 1).Value & """, ""Age"": " & ws.Cells(cell.Row, 2).Value & "}"
    ' 仅当前行不是最后一行时添加逗号
    If cell.Row <> lastRow Then
        jsonStr = jsonStr & ","
    End If
Next cell

方案2：用数组存储项后再拼接（推荐） #

将每个JSON对象存入数组，使用Join方法自动用逗号连接，从根源避免末尾逗号：

Dim jsonItems() As String
ReDim jsonItems(1 To lastRow - 1)  ' 从第2行开始，共lastRow-1个数据项

For i = 2 To lastRow
    jsonItems(i - 1) = "{""Name"": """ & ws.Cells(i, 1).Value & """, ""Age"": " & ws.Cells(i, 2).Value & "}"
Next i

jsonStr = "[" & Join(jsonItems, ",") & "]"

方案3：循环结束后移除末尾多余逗号 #

如果坚持原循环逻辑，在闭合数组前检查并删除最后一个逗号：

' 循环结束后处理
If Len(jsonStr) > 1 And Right(jsonStr, 1) = "," Then
    jsonStr = Left(jsonStr, Len(jsonStr) - 1)
End If
jsonStr = jsonStr & "]"

内容的提问来源于stack exchange，提问作者Brad Lang