It's the UPS standard request for getting shipping rates, looks like this:Where is the data for the body coming from? Can you use a loop?
Dim ShipFromCity As String
Dim json As Object
Set json = ParseJson(myJsonString)
ShipFromCity= json("RateRequest")("Shipment")("ShipFrom")("Address")("City")
With the library I linked, getting the City from ShipFrom would be like this:
Code:Dim ShipFromCity As String Dim json As Object Set json = ParseJson(myJsonString) ShipFromCity= json("RateRequest")("Shipment")("ShipFrom")("Address")("City")
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
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
.SetRequestHeader "Authorization", "Bearer " & MyApiKey
.SetRequestHeader "Content-Type", "application/json; charset=UTF-8"
.Send myDictionary
I would use the json parser.I would like to build the request body without having to append & _ to each line of the string.
s = s & "..."
s = s & """ShipTo"": {"
s = s & """Name"": """ & Data("ShipToName") & ""","
s = s & """Address"": {"
s = s & """AddressLine"": """","
s = s & """City"": """ & Data("ShipToCity") & ""","
s = s & "..."
With New StringCollection
.Add "..."
.Add """ShipTo"": {"
.Add """Name"": """ & Data("ShipToName") & ""","
.Add """Address"": {"
.Add """AddressLine"": """","
.Add """City"": """ & Data("ShipToCity") & ""","
.Add "..."
JsonString = .ToString("")
End With
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
JsonString = DLookup("TemplateString", "JsonTemplates", "Id='UPS standard request'")
For Each DataItem In Data
JsonString = Replace(JsonString, "{%" & DataItem.Name & "%}", DataItem.Value)
Next
Thus trading an underscore for a heap of extra typing that distracts from the actual code.I tend to use the format of
strSQL = strSQL & "......."
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.I would like to build the request body without having to append & _ to each line of the string.