Sub JSONを読み込む()
    Dim jsonPath As String
    Dim mCode As Variant
    Dim formulaText As String
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim i As Long

    On Error Resume Next

    For i = ThisWorkbook.Connections.Count To 1 Step -1
        ThisWorkbook.Connections(i).Delete
    Next i

    For i = ThisWorkbook.Queries.Count To 1 Step -1
        ThisWorkbook.Queries(i).Delete
    Next i

    On Error GoTo 0

    jsonPath = ThisWorkbook.Path & "\data.json"

    mCode = Array( _
        "let", _
        "    ソース = Json.Document(File.Contents(""" & jsonPath & """)),", _
        "    レコード一覧 = Table.FromRecords(ソース),", _
        "    列名変更 = Table.RenameColumns(レコード一覧, {{""id"", ""ID""}, {""question"", ""問題""}, {""correct"", ""正解""}, {""example"", ""例題""}, {""translation"", ""翻訳""}}),", _
        "    列順変更 = Table.ReorderColumns(列名変更, {""ID"", ""問題"", ""a"", ""b"", ""c"", ""d"", ""正解"", ""例題"", ""翻訳""}),", _
        "    型変換 = Table.TransformColumnTypes(列順変更, {{""ID"", Int64.Type}, {""問題"", type text}, {""a"", type text}, {""b"", type text}, {""c"", type text}, {""d"", type text}, {""正解"", type text}, {""例題"", type text}, {""翻訳"", type text}})", _
        "in", _
        "    型変換" _
    )

    formulaText = Join(mCode, vbCrLf)

    ThisWorkbook.Queries.Add _
        Name:="JSON読み込み", _
        Formula:=formulaText

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lo = ws.ListObjects.Add( _
        SourceType:=0, _
        Source:=Array( _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
            "Data Source=$Workbook$;" & _
            "Location=""JSON読み込み"";" _
        ), _
        Destination:=ws.Range("A1") _
    )

    lo.Name = "T_TOEIC"

    With lo.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [JSON読み込み]")
        .Refresh BackgroundQuery:=False
    End With
       
    On Error Resume Next

    For i = ThisWorkbook.Connections.Count To 1 Step -1
        ThisWorkbook.Connections(i).Delete
    Next i

    For i = ThisWorkbook.Queries.Count To 1 Step -1
        ThisWorkbook.Queries(i).Delete
    Next i
End Sub
