(Excel) UPS Tracking API

Demonstrates making a call to the UPS tracking REST API. Parses the tracking response and extracts the base64 signature image to a gif file.

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

' This is the testing endpoint for the tracking API:

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

' Send an HTTP request with the following JSON body:

' {
'   "UPSSecurity": {
'     "UsernameToken": {
'       "Username": "Your Username",
'       "Password": "Your Password"
'     },
'     "ServiceAccessToken": {
'       "AccessLicenseNumber": "Your Access License Number"
'     }
'   },
'   "TrackRequest": {
'     "Request": {
'       "RequestOption": "1",
'       "TransactionReference": {
'         "CustomerContext": "Your Test Case Summary Description"
'       }
'     },
'     "InquiryNumber": "YourTrackingNumber"
'   }
' }
' 

' Build the above JSON.
Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
success = json.UpdateString("UPSSecurity.UsernameToken.Username","UPS_USERNAME")
success = json.UpdateString("UPSSecurity.UsernameToken.Password","UPS_PASSWORD")
success = json.UpdateString("UPSSecurity.ServiceAccessToken.AccessLicenseNumber","UPS_ACCESS_KEY")
' Request all activity...
success = json.UpdateString("TrackRequest.Request.RequestOption","activity")
success = json.UpdateString("TrackRequest.Request.TransactionReference.CustomerContext","Your Test Case Summary Description")
success = json.UpdateString("TrackRequest.InquiryNumber","1Z12345E0205271688")

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()

' {
'   "TrackResponse": {
'     "Response": {
'       "ResponseStatus": {
'         "Code": "1",
'         "Description": "Success"
'       },
'       "TransactionReference": {
'         "CustomerContext": "Your Test Case Summary Description"
'       }
'     },
'     "Shipment": {
'       "InquiryNumber": {
'         "Code": "01",
'         "Description": "ShipmentIdentificationNumber",
'         "Value": "1Z12345E0205271688"
'       },
'       "ShipmentType": {
'         "Code": "01",
'         "Description": "Small Package"
'       },
'       "ShipperNumber": "12345E",
'       "Service": {
'         "Code": "002",
'         "Description": "2ND DAY AIR"
'       },
'       "ReferenceNumber": {
'         "Code": "01",
'         "Value": "LINE4AND115"
'       },
'       "PickupDate": "19990608",
'       "Package": {
'         "TrackingNumber": "1Z12345E0205271688",
'         "Activity": [
'           {
'             "ActivityLocation": {
'               "Address": {
'                 "City": "ANYTOWN",
'                 "StateProvinceCode": "GA",
' 
'                 "PostalCode": "30340",
'                 "CountryCode": "US"
'               },
'               "Code": "ML",
'               "Description": "BACK DOOR",
'               "SignedForByName": "JOHN DOE"
'             },
'             "Status": {
'               "Type": "D",
'               "Description": "DELIVERED",
'               "Code": "KM"
'             },
'             "Date": "19990610",
'             "Time": "120000",
'             "Document": {
'               "Type": {
'                 "Code": "01",
'                 "Description": "Signature Image"
'               },
'               "Content": "R0lGODdhoA ... JU9Y8RdHsRKLMVJ4MVDMREAAADs=",
'               "Format": {
'                 "Code": "01",
'                 "Description": "GIF"
'               }
'             }
'           },
'           {
'             "Status": {
'               "Type": "M",
'               "Description": "BILLING INFORMATION RECEIVED. SHIPMENT DATE PENDING.",
'               "Code": "MP"
'             },
'             "Date": "19990608",
'             "Time": "120000"
'           }
'         ],
'         "PackageWeight": {
'           "UnitOfMeasurement": {
'             "Code": "LBS"
'           },
'           "Weight": "5.00"
'         },
'         "ReferenceNumber": [
'           {
'             "Code": "01",
'             "Value": "LINE4AND115"
'           },
'           {
'             "Code": "08",
'             "Value": "LJ67Y5"
'           }
'         ]
'       }
'     },
'     "Disclaimer": "You are using UPS tracking service on customer integration test environment, please switch to UPS production environment once you finish the test. The URL is https://onlinetools.ups.com/webservices/Track"
'   }
' }

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


statusCode = json.StringOf("TrackResponse.Response.ResponseStatus.Code")

statusDescription = json.StringOf("TrackResponse.Response.ResponseStatus.Description")
Debug.Print "statusCode: "; statusCode
Debug.Print "statusDescription"; statusDescription

customerContext = json.StringOf("TrackResponse.Response.TransactionReference.CustomerContext")

inquiryNumberCode = json.StringOf("TrackResponse.Shipment.InquiryNumber.Code")

inquiryNumberDescription = json.StringOf("TrackResponse.Shipment.InquiryNumber.Description")

inquiryNumberValue = json.StringOf("TrackResponse.Shipment.InquiryNumber.Value")

shipmentTypeCode = json.StringOf("TrackResponse.Shipment.ShipmentType.Code")

shipmentTypeDescription = json.StringOf("TrackResponse.Shipment.ShipmentType.Description")

shipperNumber = json.StringOf("TrackResponse.Shipment.ShipperNumber")

serviceCode = json.StringOf("TrackResponse.Shipment.Service.Code")

serviceDescription = json.StringOf("TrackResponse.Shipment.Service.Description")

referenceNumberCode = json.StringOf("TrackResponse.Shipment.ReferenceNumber.Code")

referenceNumberValue = json.StringOf("TrackResponse.Shipment.ReferenceNumber.Value")

pickupDate = json.StringOf("TrackResponse.Shipment.PickupDate")

trackingNumber = json.StringOf("TrackResponse.Shipment.Package.TrackingNumber")

unitOfMeasurementCode = json.StringOf("TrackResponse.Shipment.Package.PackageWeight.UnitOfMeasurement.Code")

weight = json.StringOf("TrackResponse.Shipment.Package.PackageWeight.Weight")

disclaimer = json.StringOf("TrackResponse.Disclaimer")


i = 0

activityCount = json.SizeOfArray("TrackResponse.Shipment.Package.Activity")
Debug.Print "activityCount: "; activityCount

Do While i < activityCount
    Debug.Print "-- activity "; i
    json.I = i
    If (json.HasMember("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.City") = True) Then

        city = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.City")
        Debug.Print "city: "; city

        provinceCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.StateProvinceCode")

        postalCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.PostalCode")

        countryCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Address.CountryCode")
    End If


    locationCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Code")

    locationDescription = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.Description")

    locationSignedForByName = json.StringOf("TrackResponse.Shipment.Package.Activity[i].ActivityLocation.SignedForByName")


    activityStatusType = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Status.Type")
    Debug.Print "activityStatusType: "; activityStatusType

    activityStatusDescription = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Status.Description")
    Debug.Print "activityStatusDescription: "; activityStatusDescription

    activityStatusCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Status.Code")
    Debug.Print "activityStatusCode: "; activityStatusCode


    activityDate = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Date")

    activityTime = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Time")

    If (json.HasMember("TrackResponse.Shipment.Package.Activity[i].Document") = True) Then

        typeCode = json.IntOf("TrackResponse.Shipment.Package.Activity[i].Document.Type.Code")

        typeDescription = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Document.Type.Description")

        documentContent = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Document.Content")

        documentFormatCode = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Document.Format.Code")
        ' Format description would be something like "GIF" for a signature image.

        documentFormatDescription = json.StringOf("TrackResponse.Shipment.Package.Activity[i].Document.Format.Description")

        ' 01 - Signature Image
        ' 02 - Delivery Receipt
        ' 03 - Free Astray
        ' 04 - POD
        If (typeCode = 1) Then
            ' We have a signature image.  Get the image data and save to a file.
            Dim sbImagePath As Chilkat.StringBuilder
            Set sbImagePath = Chilkat.NewStringBuilder
            success = sbImagePath.Append("qa_output/sig_")
            success = sbImagePath.Append(trackingNumber)
            success = sbImagePath.Append(".")
            success = sbImagePath.Append(documentFormatDescription)
            Dim imageData As Chilkat.BinData
            Set imageData = Chilkat.NewBinData
            success = imageData.AppendEncoded(documentContent,"base64")
            ' Write to "qa_output/sig_1Z12345E0205271688.GIF"
            success = imageData.WriteFile(sbImagePath.GetAsString())
        End If

    End If

    i = i + 1
Loop

i = 0

refnumCount = json.SizeOfArray("TrackResponse.Shipment.Package.ReferenceNumber")
Do While i < refnumCount
    json.I = i

    refnumCode = json.StringOf("TrackResponse.Shipment.Package.ReferenceNumber[i].Code")

    refnumValue = json.StringOf("TrackResponse.Shipment.Package.ReferenceNumber[i].Value")
    i = i + 1
Loop

Debug.Print "Success."