Can you do an API call in Excel using VBA?

[Elliottj2121]

Hello,

Is there a way to do an API call in excel using VBA? or is there another non-macro way to do it? I am trying to use the United States Post Office's API to get the full nine digit zip code for addresses. The API requires a username and password which I have. I have linked to the technical information on the USPS website and included an example sheet. Any help would be greatly appreciated.

USPS TECH SPEC SHEET Specific section is 3.0 ZIP Code Lookup API

Book1
	A	B	C	D
1	VARIABLE	DATA	RESULT	DATA
2	ADDRESS	1600 PENNSYLVANIA AVE NW	ZIP+4	20500-0005
3	CITY	WASHINGTON
4	STATE	DC
5	ZIP	20500
6
7
Sheet1

 

[skillilea]

I got this close but the requestData seems to fail on the call.

Sub GetUSPSAddressInfo()
    Dim apiURL As String
    Dim address As String
    Dim requestData As String
    Dim responseText As String
    
    apiURL = "https://secure.shippingapis.com/ShippingAPI.dll/API/Verify"
    
    address = "1600 Amphitheatre Parkway, Mountain View, CA 94043, USA"
    
    ' this is where it fails
    requestData = "<AddressValidateRequest USERID=''><Address ID='0'><Address1></Address1><Address2><![CDATA[" & address & "]]></Address2><City></City><State></State><Zip5></Zip5><Zip4></Zip4></Address></AddressValidateRequest>"
    
    responseText = GetUSPSAPIResponse(apiURL, requestData)
    
    Debug.Print "XML Response:"
    Debug.Print responseText
End Sub

Function GetUSPSAPIResponse(apiURL As String, requestData As String) As String
    Dim xmlHTTP As Object
    Set xmlHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    With xmlHTTP
        .Open "POST", apiURL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send "API=Verify&XML=" & requestData
        If .Status = 200 Then
            GetUSPSAPIResponse = .responseText
        Else
            GetUSPSAPIResponse = "Error " & .Status & ": " & .responseText
        End If
    End With
    
    Set xmlHTTP = Nothing
End Function

 

[John_w]

USPS TECH SPEC SHEET Specific section is 3.0 ZIP Code Lookup API

See if this example code works for you - change the userID string. According to the specs a password isn't needed.

Public Sub Zip_Code_Lookup()

    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    
    Dim URL As String
    Dim userID As String
    Dim XML As String
    
    userID = "user123"
    
    XML = "<ZipCodeLookupRequest USERID='" & userID & "'>" & _
          "<Address ID='" & "1" & "'>" & _
          "<Address1></Address1>" & _
          "<Address2>1600 PENNSYLVANIA AVE NW</Address2>" & _
          "<City>WASHINGTON</City>" & _
          "<State>DC</State>" & _
          "<Zip5>20500</Zip5>" & _
          "<Zip4></Zip4>" & _
          "</Address>" & _
          "</ZipCodeLookupRequest>"
       
    URL = "https://secure.shippingapis.com/ShippingAPI.dll?API=ZipCodeLookup&XML=" & XML
          
    With httpReq
        .Open "GET", URL, False
        .send
        Debug.Print .Status, .statusText
        Debug.Print .responseText
    End With

End Sub

Your code would have to build the XML string according to the cell values. Since the response is XML format, it's probably better to use DOMDocument instead of XMLHTTP to send the request and then the response can be easily parsed.