下記のVBAコードを使い、下記のエクセルファイルを
kintoneに自動でレコードに読み込みたいと思っております。
しかし、実行すると（Data has been imported to kintone successfully）とメッセージが返ってくるのですが、kintoneにはレコードが追加されていません。
なぜかわかる方教えて頂きたいです。
よろしくお願いします。
kintoneのフィールドは
納品日＝日付、配送先番号＝ルックアップ、重量＝数値
| 納品日 | 配送先番号 |
重量
|
| 2023/4/22 | あんず | 500 |
| 2023/4/22 | ポン酢 | 400 |
Sub ImportDataToKintone()
Dim lastRow As Long
Dim i As Long
Dim deliveryDate As String
Dim destinationCode As String
Dim weight As String
Dim filePath As String
Dim wb As Workbook
Dim ws As Worksheet
Dim xmlHttpRequest As New MSXML2.ServerXMLHTTP60
Dim url As String
Dim payload As String
’ Set your kintone subdomain, API token and App ID
Const subDomain As String = “aaaabbbbcccc”
Const apiToken As String = “ErgccvYCGVkvv46453”
Const appId As String = “815”
’ Set kintone API URL
url = “https://” & subDomain & “.cybozu.com/k/v1/records.json”
’ Open the file selection dialog
With Application.FileDialog(msoFileDialogFilePicker)
.Title = “Select an Excel file”
.Filters.Clear
.Filters.Add “Excel Files”, “*.xls; *.xlsx; *.xlsm”
If .Show = -1 Then
filePath = .SelectedItems(1)
Else
MsgBox “No file was selected. Exiting…”
Exit Sub
End If
End With
’ Open the selected Excel file
Set wb = Workbooks.Open(filePath)
Set ws = wb.Worksheets(1)
’ Find the last row
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
’ Loop through each row of data
For i = 2 To lastRow
’ Read data from the worksheet
deliveryDate = Format(ws.Cells(i, 1).Value, “yyyy-MM-dd”)
destinationCode = ws.Cells(i, 2).Value
weight = ws.Cells(i, 3).Value
’ Create JSON payload for kintone API
payload = “{”“app”“:”“” & appId & “”“,”“record”“:{”“DeliveryDate”“:{”“value”“:”“” & deliveryDate & “”“},”“DestinationCode”“:{”“value”“:”“” & destinationCode & “”“},”“Weight”“:{”“value”“:”“” & weight & “”“}}}”
’ Send a POST request to kintone API
With xmlHttpRequest
.Open “POST”, url, False
.setRequestHeader “Content-Type”, “application/json”
.setRequestHeader “X-Cybozu-API-Token”, apiToken
.send payload
’ Check if the request was successful
If .Status = 200 Then
Debug.Print “Record for row " & i & " has been added to kintone.”
Else
Debug.Print "Error in adding record for row " & i & ": " & .Status & " " & .statusText
End If
End With
Next i
’ Close the workbook
wb.Close SaveChanges:=False
MsgBox “Data has been imported to kintone successfully.”
End Sub