Sub Macro1()
'型定義
Dim wb As Workbook
Dim ws As Worksheet
Dim startDt As Date
Dim endDt As Date
Dim countd As Integer
Dim timestamp As String
Dim A As String
Dim B As String
Dim C As String
Dim K As String
Dim jsonData As String
Dim jsonOutput As String
Dim jsonList As String
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
'日付範囲の設定 CDate = 文字列2023/2/2をDate型に変換
startDt = CDate(ws.Range("B1").Value)
endDt = CDate(ws.Range("C1").Value)
'JSONデータの生成
For D = startDt To endDt '(例: 2023/2/2~2023/2/5)分ループ
'データの取得
timestamp = Format(D, "yyyymmdd")
A = ws.Range("B2").Value
B = ws.Range("B3").Value
C = ws.Range("B4").Value
K = ws.Range("B6").Value
countd = ws.Range("B5").Value 'ネストJSON数
'JSONデータの生成
jsonList = ""
If countd > 0 Then
For i = 1 To countd
jsonList = jsonList & """count" & i & """:{""あいう"": " & K & "}"
If i < countd Then
jsonList = jsonList & "," 'jsonList生成した"count1": {"K": 4000}を「,」で繋ぐ
End If
Next i
jsonList = """list"": {" & jsonList & "}" '"count1": {"あいう": 4000},"count2": {"あいう": 4000}
Else
jsonList = """list"": {""number"": 0, ""D"": " & D & "}" '←無くても良い。
End If
'取得したデータまとめる
jsonData = "{""timeStamp"": " & timestamp & ", ""A"": " & A & ", ""B"": " & B & ", ""C"": " & C & ", " & jsonList & "}"
'JSONデータを配列に追加
jsonOutput = jsonOutput & jsonData
If D < endDt Then
jsonOutput = jsonOutput & ","
End If
Next D 'ループ終了
'JSONデータの出力
Open "C:\Users\socia\OneDrive\デスクトップ\output.json" For Output As #1
Print #1, "[" & jsonOutput & "]"
Close #1
MsgBox "JSONデータ生成完了"
End Sub