Link to home
Start Free TrialLog in
Avatar of hthart
hthartFlag for United States of America

asked on

Verify a street address through USPS using Excel VBA

I need to verify a street address using a VBA macro in Excel by accessing the United States Post Office (USPS).

I found this macro on the Excel Forum that almost works.  It gets halfway through and errors on at least two lines of code.

Here is the Subroutine:

Sub USPS()
        Dim eRow As Long
        Dim ele As Object
        'Dim IE As New InternetExplorer
        Sheets("Address").Select
       
        eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Set objie = CreateObject("InternetExplorer.Application")
       
        ' Verify 10 addresses      
         For r = 2 To 11
       
        myaddress = Cells(r, 1).Value
        mycity = Cells(r, 3).Value
        mystate = Cells(r, 4).Value
        myzipcode = Cells(r, 5).Value
       
        'myaddress = Range("a2").Value
        'mycity = Range("c2").Value
        'mystate = Range("d2").Value
        'myzipcode = Range("e2").Value
       
       
        With objie
        .Visible = True
        .navigate "https://tools.usps.com/go/ZipLookupAction!input.action"
        Do While .Busy Or _
        .readyState <> 4
        DoEvents
        Loop
        Set what = .document.getElementsByName("tAddress")
        what.Item(0).Value = myaddress
        Set zipcode = .document.getElementsByName("tCity")
        zipcode.Item(0).Value = mycity
        Set zipcode1 = .document.getElementsByName("sState")
        zipcode1.Item(0).Value = mystate
'        Set zipcode2 = .document.getElementsByName("Zzip")
'        zipcode2.Item(0).Value = myzipcode

        .document.getElementById("lookupZipFindBtn").Click
       
        Do While .Busy Or _
        .readyState <> 4
        DoEvents
        Loop
       
       
        For Each ele In .document.all
        Select Case ele.className
       
        Case "address1 range"
       Worksheets("Address").Cells(r, 7).Value = ele.innerText
       
        Case "city range"
        Worksheets("Address").Cells(r, 8).Value = ele.innerText
       
        Case "state range"
        Worksheets("Address").Cells(r, 9).Value = ele.innerText
       
        Case "zip"
        Worksheets("Address").Cells(r, 10).Value = ele.innerText
       
        Case "zip4"
        Worksheets("Address").Cells(r, 11).Value = ele.innerText
       
        End Select
        '
        Next ele
        End With
       
        Next r
        Set objie = Nothing
        Set ele = Nothing
        Set IE = Nothing
           
        'IE.Quit


End Sub


It errors on the following two lines of code:

       zipcode1.Item(0).Value = mystate
and
       .document.getElementById("lookupZipFindBtn").Click

It populated the street address and city just fine, but won't populate the state and zip code.  And I could not get it to click the "Find" button.

I tried using mystate="TX - Texas", just like the option in the dropdown menu on the webpage, but it didn't take it.

It give me the following error for both lines of code:
       Run-time error '91'
       Object variable or With block variable not set


1. I need it to populate the state and zip code.
2. I need it to click the Find button
3. I need it to return the "County" along with everything else the code says to return.

Thank you for your assistance,.  I appreciate any help with this issue.
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Could you post a sample of the Excel sheet you use?
ASKER CERTIFIED SOLUTION
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hthart

ASKER

xtermie

Thank you for the advice and information.  It is helpful.  

I need and, now have, is a VBA subroutine that scrapes the USPS website.