VBA and Json POST MS Access

Question

要使用api，我想在VBA中制作一个Post。帖子本身是有效的，但请求中Json的结构化正文对我不起作用。如何在VBA(Access(中结构化这些数据下面的VBA用于帖子，"sEnv"变量中的数据应以其他格式结构化。但是怎么做呢？

Sub confirm()
Dim sMsg As String
Dim sURL As String
Dim sEnv As String
Dim body As New Dictionary

BC = ""
response = ""
'Set and Instantiate our working objects
Set barcode_info = New MSXML2.XMLHTTP60
C_num = CUST_NUMBER
C_code = CUST_CODE
Key = API_KEY

sEnv = "
{
"Customer": {
"Address": {
"AddressType": "02",
"City": "Hoofddorp",
"CompanyName": "PostNL",
"Countrycode": "NL",
"HouseNr": "42",
"Street": "Siriusdreef",
"Zipcode": "2132WT"
},
"CollectionLocation": "1234506",
"ContactPerson": "Janssen",
"CustomerCode": "ABZL",
"CustomerNumber": "9005728",
"Email": "[e-mail]",
"Name": "Janssen"
},
"Message": {
"MessageID": "1",
"MessageTimeStamp": "29-06-2016 12:00:00",
"Printertype": "GraphicFile|PDF"
}}""



sURL = "https://api-sandbox.postnl.nl/shipment/v2/confirm"

barcode_info.Open "POST", sURL, False
barcode_info.setRequestHeader "accept","application/json"
barcode_info.setRequestHeader "apikey", Key
barcode_info.send (sEnv)

response = barcode.responseText
'Debug.Print objhttp.responseText("barcode")

Set response = JsonConverter.ParseJson(barcode.responseText)

BC = response("Barcode", "Warnings")
MsgBox (BC)

'MsgBox (response("Barcode"))
'barcodes_ophalen = response("Barcode")

'clean up code
Set barcode = Nothing
Set xmlDoc = Nothing
Set response = Nothing

End Sub

Answer 1

最好将JSON粘贴到工作表单元格中并从中读取。

否则就是一团糟的报价：

senv = "{""Customer"": {" & _
"""Address"": {" & _
"""AddressType"": ""02""," & _
"""City"": ""Hoofddorp""," & _
"""CompanyName"": ""PostNL""," & _
"""Countrycode"": ""NL""," & _
"""HouseNr"": ""42""," & _
"""Street"": ""Siriusdreef""," & _
"""Zipcode"": ""2132WT""" & _
"}," & _
"""CollectionLocation"": ""1234506""," & _
"""ContactPerson"": ""Janssen""," & _
"""CustomerCode"": ""ABZL""," & _
"""CustomerNumber"": ""9005728""," & _
"""Email"": ""[e-mail]""," & _
"""Name"": ""Janssen""" & _
"}," & _
"""Message"": {" & _
"""MessageID"": ""1""," & _
"""MessageTimeStamp"": ""29-06-2016 12:00:00""," & _
"""Printertype"": ""GraphicFile|PDF""" & _
"}}"""