Forum Discussion
Use VBA to track parcels from USPS API
Does anyone have a module to get status and info for parcels from USPS API ?
I have created the code below, but I don't know how to get the right response :
Dim html As HTMLDocument, dateString As String
Set html = New HTMLDocument
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", "http://secure.shippingapis.com/ShippingAPI.dll?API=TrackV2&XML=<TrackRequest USERID=" & "988HONES7813" & "><TrackID ID=" & id & "></TrackID></TrackRequest>"
.send
GetRESPONSE = .responseText
- Could you try this function?
Public Function TestTrackRequest()
Dim oXML As Object
Dim oDOM As Object
Dim sHTML As String
Set oXML = CreateObject("MSXML2.XMLHTTP")
Set oDOM = CreateObject("MSXML2.DOMDocument.6.0")
With oXML
sHTML = "http://production.shippingapis.com/ShippingAPI.dll?API=TrackV2&XML=<TrackRequest USERID='988HONES7813'><TrackID ID='EJ958083578US'></TrackID></TrackRequest>"
.Open "GET", sHTML, False
.send sHTML
If .Status = "200" And .responseXML.childnodes.length > 0 Then
oDOM.loadxml (.responseText)
If oDOM.childnodes(1).tagname = "Error" Then
Debug.Print oDOM.childnodes(1).childnodes(0).Text & " - " & oDOM.childnodes(1).childnodes(1).Text
Else
Debug.Print oDOM.XML
End If
Else
Debug.Print .Status & " - " & .responseText
End If
End With
Set oXML = Nothing
Set oDOM = Nothing
End Function
- Could you try this function?
Public Function TestTrackRequest()
Dim oXML As Object
Dim oDOM As Object
Dim sHTML As String
Set oXML = CreateObject("MSXML2.XMLHTTP")
Set oDOM = CreateObject("MSXML2.DOMDocument.6.0")
With oXML
sHTML = "http://production.shippingapis.com/ShippingAPI.dll?API=TrackV2&XML=<TrackRequest USERID='988HONES7813'><TrackID ID='EJ958083578US'></TrackID></TrackRequest>"
.Open "GET", sHTML, False
.send sHTML
If .Status = "200" And .responseXML.childnodes.length > 0 Then
oDOM.loadxml (.responseText)
If oDOM.childnodes(1).tagname = "Error" Then
Debug.Print oDOM.childnodes(1).childnodes(0).Text & " - " & oDOM.childnodes(1).childnodes(1).Text
Else
Debug.Print oDOM.XML
End If
Else
Debug.Print .Status & " - " & .responseText
End If
End With
Set oXML = Nothing
Set oDOM = Nothing
End FunctionI think that it works. I get an error '80040B1A - API Authorization failure' but it seems that the userID is not valid. I'll let you know if it works when I solve the error. Thank you
- You must ask USPS for verification, see this answer for details:
https://stackoverflow.com/questions/20395069/authorization-failure-in-usps-address-verification-api
