PART 15 — JSON処理
ネイティブ非対応問題と自前実装・ライブラリ活用

Option Explicit

' 32bit Excelのみ動作
Function ParseJsonWithSC(ByVal jsonStr As String) As Object
    Dim sc As Object
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"

    ' eval でJSONをJSオブジェクトに変換
    sc.Eval "var obj = " & jsonStr
    Set ParseJsonWithSC = sc.Eval("obj")
End Function

Sub TestScriptControl()
    Dim json As String
    json = "{""name"":""田中"",""age"":30,""city"":""Tokyo""}"

    Dim obj As Object
    Set obj = ParseJsonWithSC(json)

    Debug.Print obj.GetMember("name")  ' → 田中
    Debug.Print obj.GetMember("age")   ' → 30
End Sub

' ── セットアップ ──
' 1. https://github.com/VBA-tools/VBA-JSON から JsonConverter.bas をダウンロード
' 2. VBE → ファイル → ファイルのインポートで JsonConverter.bas を追加
' 3. 参照設定：Microsoft Scripting Runtime にチェック

Option Explicit

Sub VbaJsonExample()
    ' ── パース ──
    Dim jsonStr As String
    jsonStr = "{""name"":""田中"",""scores"":[90,85,92]}"

    Dim json As Object
    Set json = JsonConverter.ParseJson(jsonStr)

    Debug.Print json("name")           ' → 田中
    Debug.Print json("scores")(1)      ' → 90（1始まり）
    Debug.Print json("scores").Count   ' → 3

    ' ── 配列を含むJSON ──
    Dim arrJson As String
    arrJson = "[{""id"":1,""name"":""A""},{""id"":2,""name"":""B""}]"

    Dim arr As Collection
    Set arr = JsonConverter.ParseJson(arrJson)

    Dim item As Object
    For Each item In arr
        Debug.Print item("id") & ": " & item("name")
    Next item

    ' ── シリアライズ ──
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict("name") = "鈴木"
    dict("age")  = 25

    Debug.Print JsonConverter.ConvertToJson(dict)
    ' → {"name":"鈴木","age":25}
End Sub

Option Explicit

Sub ProcessJsonViaPowerShell()
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")

    ' PowerShell でAPIを呼び出してJSONをCSVに変換
    Dim psCmd As String
    psCmd = "powershell -NoProfile -Command """ & _
            "Invoke-RestMethod 'https://api.example.com/data' | " & _
            "ConvertTo-Csv -NoTypeInformation | " & _
            "Out-File -FilePath 'C:\data\output.csv' -Encoding UTF8" & """"

    Dim exitCode As Long
    exitCode = wsh.Run(psCmd, 0, True)

    If exitCode = 0 Then
        ' 出力されたCSVをExcelに取り込む
        Workbooks.Open "C:\data\output.csv"
    End If

    Set wsh = Nothing
End Sub

Option Explicit

' キーと値の配列からシンプルなJSONオブジェクトを生成
Function BuildJsonObject(ParamArray pairs() As Variant) As String
    Dim sb    As String
    Dim i     As Long

    sb = "{"
    For i = 0 To UBound(pairs) - 1 Step 2
        If i > 0 Then sb = sb & ","
        Dim key As String: key = CStr(pairs(i))
        Dim val As Variant: val = pairs(i + 1)

        If IsNumeric(val) Then
            sb = sb & """" & key & """:" & val
        Else
            ' 文字列値: ダブルクォートをエスケープ
            sb = sb & """" & key & """:""" & Replace(CStr(val), """", "\""") & """"
        End If
    Next i
    sb = sb & "}"
    BuildJsonObject = sb
End Function

Sub TestBuildJson()
    Debug.Print BuildJsonObject("name", "田中", "age", 30, "city", "Tokyo")
    ' → {"name":"田中","age":30,"city":"Tokyo"}
End Sub