hthart
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("InternetExpl orer.Appli cation")
' 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.getElementsByNam e("tAddres s")
what.Item(0).Value = myaddress
Set zipcode = .document.getElementsByNam e("tCity")
zipcode.Item(0).Value = mycity
Set zipcode1 = .document.getElementsByNam e("sState" )
zipcode1.Item(0).Value = mystate
' Set zipcode2 = .document.getElementsByNam e("Zzip")
' zipcode2.Item(0).Value = myzipcode
.document.getElementById(" lookupZipF indBtn").C lick
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
For Each ele In .document.all
Select Case ele.className
Case "address1 range"
Worksheets("Address").Cell s(r, 7).Value = ele.innerText
Case "city range"
Worksheets("Address").Cell s(r, 8).Value = ele.innerText
Case "state range"
Worksheets("Address").Cell s(r, 9).Value = ele.innerText
Case "zip"
Worksheets("Address").Cell s(r, 10).Value = ele.innerText
Case "zip4"
Worksheets("Address").Cell s(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(" lookupZipF indBtn").C lick
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.
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("InternetExpl
' 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.getElementsByNam
what.Item(0).Value = myaddress
Set zipcode = .document.getElementsByNam
zipcode.Item(0).Value = mycity
Set zipcode1 = .document.getElementsByNam
zipcode1.Item(0).Value = mystate
' Set zipcode2 = .document.getElementsByNam
' zipcode2.Item(0).Value = myzipcode
.document.getElementById("
Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
For Each ele In .document.all
Select Case ele.className
Case "address1 range"
Worksheets("Address").Cell
Case "city range"
Worksheets("Address").Cell
Case "state range"
Worksheets("Address").Cell
Case "zip"
Worksheets("Address").Cell
Case "zip4"
Worksheets("Address").Cell
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("
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.
Could you post a sample of the Excel sheet you use?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Thank you for the advice and information. It is helpful.
I need and, now have, is a VBA subroutine that scrapes the USPS website.