Building a JSON Request Body in VBA (1 Viewer)

[yellenick]

I am trying to call an API. To do so I need to construct a JSON request body.

What is the best way to do this? Is there a library I can use to prevent using "& _" on every line?

 

[Gasman]

I tend to use the format of

strSQL = strSQL & "......."

when building sql strings and not continuation characters.

 

[theDBguy]

Where is the data for the body coming from? Can you use a loop?

 

[561414]

I use this library:
https://github.com/VBA-tools/VBA-JSON

Drag JsonConverter.bas into your project and use both ConvertToJson and ParseJson functions to use it on the response you get.

 

[yellenick]

Where is the data for the body coming from? Can you use a loop?

It's the UPS standard request for getting shipping rates, looks like this:

{
"RateRequest": {
"Request": {
"RequestOption": "Rate",
"TransactionReference": {
"CustomerContext": "CustomerContext",
"TransactionIdentifier": "TransactionIdentifier"
}
},
"Shipment": {
"Shipper": {
"Name": "ShipperName",
"ShipperNumber": "XXXXX",
"Address": {
"AddressLine": "",
"City": "TIMONIUM",
"StateProvinceCode": "MD",
"PostalCode": "21093",
"CountryCode": "US"
}
},
"ShipTo": {
"Name": "ShipToName",
"Address": {
"AddressLine": "",
"City": "Alpharetta",
"StateProvinceCode": "GA",
"PostalCode": "30005",
"CountryCode": "US"
}
},
"ShipFrom": {
"Name": "ShipFromName",
"Address": {
"AddressLine": "",
"City": "TIMONIUM",
"StateProvinceCode": "MD",
"PostalCode": "21093",
"CountryCode": "US"
}
},
"PaymentDetails": {
"ShipmentCharge": {
"Type": "01",
"BillShipper": {
"AccountNumber": "XXXXX"
}
}
},
"Service": {
"Code": "03",
"Description": "Ground"
},
"Package": [
{
"PackagingType": {
"Code": "02"
},
"Dimensions": {
"UnitOfMeasurement": {
"Code": "IN",
"Description": "Inches"
},
"Length": "9",
"Width": "6",
"Height": "5"
},
"PackageWeight": {
"UnitOfMeasurement": {
"Code": "LBS",
"Description": "Pounds"
},
"Weight": "1"
}
}
]
}
}
}

 

[561414]

With the library I linked, getting the City from ShipFrom would be like this:

    Dim ShipFromCity As String
    Dim json As Object
    Set json = ParseJson(myJsonString)
    
    ShipFromCity= json("RateRequest")("Shipment")("ShipFrom")("Address")("City")

 

[yellenick]

With the library I linked, getting the City from ShipFrom would be like this:

    Dim ShipFromCity As String
    Dim json As Object
    Set json = ParseJson(myJsonString)
   
    ShipFromCity= json("RateRequest")("Shipment")("ShipFrom")("Address")("City")

Thank you, but I have not gotten to the parsing of the JSON yet, I am building the request body to send to the API. I would like to build the request body without having to append & _ to each line of the string.

Any ideas?

 

[561414]

Yes, just put it in a dictionary then use the ConvertToJson function of the library. I have a procedure here, I removed some things, but you can get the idea, I hope.

This is how you could send the dictionary.

Public Sub AddProduct(myProduct As Dictionary)
    Set oRequest = CreateObject("MSXML2.ServerXMLHTTP.3.0")
    With oRequest
        .Open "POST", baseURL & "someUrl"
        .Send ConvertToJson(myProduct, 4)
        strResponse = .responseText
    End With
End Sub

And this is how you could build the dictionary.

Private Sub btnSubmit_Click()
  
    Dim myProduct As Dictionary: Set myProduct = New Dictionary
    myProduct.Add "ProductDescription", Me.txtProductDescription.Value
    myProduct.Add "Unit", Me.txtUnit.Value
    myProduct.Add "Price", Me.txtPrice.Value
  
    If Me.OpenArgs = "Add" Then
        AddProduct myProduct
    ElseIf Not IsNull(Me.OpenArgs) Then
        EditProduct myProduct, Me.OpenArgs
    End If
    GetProducts
    Forms!frmProducts.Form!frmProductsList.Requery
    DoCmd.Close
End Sub

 

[561414]

It depends on the endpoint requirements, though. Some of them may require you to do something like this:

.SetRequestHeader "Authorization", "Bearer " & MyApiKey
.SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
.Send myDictionary

 

[isladogs]

You could also look at my JSON app which will do almost all of the work for you.

JSON Analyse & Transform

JSON Analyse & Transform for Access makes importing and handling JSON files a straightforward and largely automated task.

www.isladogs.co.uk

 

[Josef P.]

I would like to build the request body without having to append & _ to each line of the string.

I would use the json parser.

Still, a few alternatives:
1)

s = s & "..."
s = s & """ShipTo"": {"
s = s & """Name"": """ & Data("ShipToName") & ""","
s = s & """Address"": {"
s = s & """AddressLine"": """","
s = s & """City"": """ & Data("ShipToCity") & ""","
s = s & "..."

2)

With New StringCollection
   .Add "..."
   .Add """ShipTo"": {"
   .Add """Name"": """ & Data("ShipToName") & ""","
   .Add """Address"": {"
   .Add """AddressLine"": """","
   .Add """City"": """ & Data("ShipToCity") & ""","
   .Add "..."

   JsonString = .ToString("")
End With

3a)

Const JsonTemplate As String = "..." & _
   """ShipTo"": {" & _
   """Name"": ""{%ShipToName%}""," & _
   """Address"": {" & _
   """AddressLine"": """"," & _
   """City"": ""{%ShipToCity%}""," & _
   "..."

JsonString = JsonTemplate

For Each DataItem In Data
   JsonString = Replace(JsonString, "{%" & DataItem.Name & "%}", DataItem.Value)
Next

3b)

JsonString = DLookup("TemplateString", "JsonTemplates", "Id='UPS standard request'")
For Each DataItem In Data
   JsonString = Replace(JsonString, "{%" & DataItem.Name & "%}", DataItem.Value)
Next

 

[Galaxiom]

I tend to use the format of

strSQL = strSQL & "......."

Thus trading an underscore for a heap of extra typing that distracts from the actual code.

 

[Pat Hartman]

I would like to build the request body without having to append & _ to each line of the string.

You could type the format into a long text field that is saved to a table. Include placeholders for each piece of data. Then you would read the data and use a list of Replace() functions to replace the placeholders with each value. Then pass the string to the API.