SOLVED

Use VBA to track parcels from USPS API

Copper Contributor

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

4 Replies
best response confirmed by Katerina7 (Copper Contributor)
Solution
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

I 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

Thank you. I will.
1 best response

Accepted Solutions
best response confirmed by Katerina7 (Copper Contributor)
Solution
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

View solution in original post