(Excel) UPS Address Validation (City, State, Zip)

Demonstrates making a call to the UPS address validation REST API.

Download Excel Class Modules Chilkat Excel Class Modules

' This example requires the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp


url = "https://wwwcie.ups.com/rest/AV"

' Send an HTTP request with the following JSON body:

' {
'   "AccessRequest": {
'     "AccessLicenseNumber": "Your Access License Number",
'     "UserId": "Your Username",
'     "Password": "Your Password"
'   },
'   "AddressValidationRequest": {
'     "Request": {
'       "TransactionReference": {
'         "CustomerContext": "Your Customer Context"
'       },
'       "RequestAction": "AV"
'     },
'     "Address": {
'       "City": "ALPHARETTA",
'       "StateProvinceCode": "GA",
'       "PostalCode": "30005"
'     }
'   }
' }

' Build the above JSON.
Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
success = json.UpdateString("AccessRequest.AccessLicenseNumber","UPS_ACCESS_KEY")
success = json.UpdateString("AccessRequest.UserId","UPS_USERNAME")
success = json.UpdateString("AccessRequest.Password","UPS_PASSWORD")
success = json.UpdateString("AddressValidationRequest.Request.TransactionReference.CustomerContext","Your Customer Context")
success = json.UpdateString("AddressValidationRequest.Request.RequestAction","AV")
success = json.UpdateString("AddressValidationRequest.Address.City","ALPHARETTA")
' We're making an intentional mistake here by passing CA instead of GA.
success = json.UpdateString("AddressValidationRequest.Address.StateProvinceCode","CA")
success = json.UpdateString("AddressValidationRequest.Address.PostalCode","30005")

Dim sb As Chilkat.StringBuilder
Set sb = Chilkat.NewStringBuilder

Set resp = http.PostJson3(url,"application/json",json)
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Debug.Print "status = "; resp.StatusCode

' A 200 response status indicate success.
If (resp.StatusCode <> 200) Then
    Debug.Print resp.BodyStr
    Debug.Print "Failed."

    Exit Sub
End If

success = json.Load(resp.BodyStr)
json.EmitCompact = False
Debug.Print json.Emit()

' A successful exact response looks like this:

' {
'   "AddressValidationResponse": {
'     "Response": {
'       "TransactionReference": {
'         "CustomerContext": "Your Customer Context"
'       },
'       "ResponseStatusCode": "1",
'       "ResponseStatusDescription": "Success"
'     },
'     "AddressValidationResult": {
'       "Rank": "1",
'       "Quality": "1.0",
'       "Address": {
'         "City": "ALPHARETTA",
'         "StateProvinceCode": "GA"
'       },
'       "PostalCodeLowEnd": "30005",
'       "PostalCodeHighEnd": "30005"
'     }
'   }
' }
' 

' A successful response that was not an exact match provides an array of closest matches, like this:

' {
'   "AddressValidationResponse": {
'     "Response": {
'       "TransactionReference": {
'         "CustomerContext": "Your Customer Context"
'         "Quality": "0.9875",
'         "Address": {
'       },
'       "ResponseStatusCode": "1",
'       "ResponseStatusDescription": "Success"
'     },
'     "AddressValidationResult": [
'       {
'         "Rank": "1",
'           "City": "ALPHARETTA",
'           "StateProvinceCode": "GA"
'         },
'         "PostalCodeLowEnd": "30005",
'         "PostalCodeHighEnd": "30005"
'       },
'       {
'         "Rank": "2",
'         "Quality": "0.9750",
'         "Address": {
'           "City": "ALPHARETTA",
'           "StateProvinceCode": "GA"
'         },
'         "PostalCodeLowEnd": "30004",
'         "PostalCodeHighEnd": "30004"
'       },
'       {
'         "Rank": "3",
'         "Quality": "0.9750",
'         "Address": {
'           "City": "ALPHARETTA",
'           "StateProvinceCode": "GA"
'         },
'         "PostalCodeLowEnd": "30009",
'         "PostalCodeHighEnd": "30009"
'       }
'     ]
'   }
' }

' Use the online tool at Generate JSON Parsing Code
' to generate JSON parsing code.


numResults = json.SizeOfArray("AddressValidationResponse.AddressValidationResult")
If (numResults < 0) Then

    ' Here's parse code for the above JSON exact response:
    customerContext = json.StringOf("AddressValidationResponse.Response.TransactionReference.CustomerContext")
    statusCode = json.StringOf("AddressValidationResponse.Response.ResponseStatusCode")
    statusDescription = json.StringOf("AddressValidationResponse.Response.ResponseStatusDescription")
    resultRank = json.StringOf("AddressValidationResponse.AddressValidationResult.Rank")
    resultQuality = json.StringOf("AddressValidationResponse.AddressValidationResult.Quality")
    city = json.StringOf("AddressValidationResponse.AddressValidationResult.Address.City")
    provinceCode = json.StringOf("AddressValidationResponse.AddressValidationResult.Address.StateProvinceCode")
    postalCodeLowEnd = json.StringOf("AddressValidationResponse.AddressValidationResult.PostalCodeLowEnd")
    postalCodeHighEnd = json.StringOf("AddressValidationResponse.AddressValidationResult.PostalCodeHighEnd")

    Debug.Print "Exact match!"
    Debug.Print "postal code: "; postalCodeLowEnd

Else

    Debug.Print "Non-Exact match."

    customerContext = json.StringOf("AddressValidationResponse.Response.TransactionReference.CustomerContext")
    statusCode = json.StringOf("AddressValidationResponse.Response.ResponseStatusCode")
    statusDescription = json.StringOf("AddressValidationResponse.Response.ResponseStatusDescription")

    i = 0
    Do While i < numResults
        json.I = i
        rank = json.StringOf("AddressValidationResponse.AddressValidationResult[i].Rank")
        Debug.Print "rank: "; rank
        quality = json.StringOf("AddressValidationResponse.AddressValidationResult[i].Quality")
        addressCity = json.StringOf("AddressValidationResponse.AddressValidationResult[i].Address.City")
        Debug.Print "addressCity: "; addressCity
        addressStateProvinceCode = json.StringOf("AddressValidationResponse.AddressValidationResult[i].Address.StateProvinceCode")
        postalCodeLowEnd = json.StringOf("AddressValidationResponse.AddressValidationResult[i].PostalCodeLowEnd")
        Debug.Print "postal code: "; postalCodeLowEnd
        postalCodeHighEnd = json.StringOf("AddressValidationResponse.AddressValidationResult[i].PostalCodeHighEnd")
        i = i + 1
    Loop
End If