ExcelテーブルをJavaScriptのconst化するVBAコード

Excelテーブルのヘッダーは”メイン”, “サブ”, “スペシャル”, “種別”, “ミラーグループ”。

これを以下の形にする。

const weapons = [
    {
        label: "TypeA",
        options: [
            {name: "name1", sub: "sub1", special: "special1", group: "group1"},
            {name: "name2", sub: "sub2", special: "special2", group: "group2"},
            {name: "name3", sub: "sub3", special: "special3", group: "group3"}
        ]
    },
    {
        label: "TypeB",
        options: [
            {name: "name4", sub: "sub4", special: "special4", group: "group4"},
            {name: "name5", sub: "sub5", special: "special5", group: "group5"}
        ]
    }
];

VBAコード

Sub ExportToJSConst()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long
    Dim lastRow As Long
    Dim currentType As String
    Dim jsString As String
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Adjust the sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    jsString = "const weapons = [" & vbNewLine
    
    For i = 2 To lastRow
        If ws.Cells(i, "D").Value <> currentType Then
            ' New type is detected
            If i <> 2 Then ' If it's not the first type
                jsString = Left(jsString, Len(jsString) - 1) ' Remove last comma
                jsString = jsString & "]" & vbNewLine & "}," & vbNewLine
            End If
            
            currentType = ws.Cells(i, "D").Value
            jsString = jsString & "    {" & vbNewLine & "        label: """ & currentType & """," & vbNewLine & "        options: [" & vbNewLine
        End If
        
        jsString = jsString & "            {name: """ & ws.Cells(i, "A").Value & """, sub: """ & ws.Cells(i, "B").Value & """, special: """ & ws.Cells(i, "C").Value & """, group: """ & ws.Cells(i, "E").Value & """}," & vbNewLine
    Next i
    
    ' Finalize the string
    jsString = Left(jsString, Len(jsString) - 1) ' Remove last comma
    jsString = jsString & "]" & vbNewLine & "}" & vbNewLine & "];" & vbNewLine
    
    ' Export to a text file or copy to the clipboard as needed
    ' For demonstration purposes, I'm just outputting it to the immediate window
    Debug.Print jsString
End Sub