Solved: Scraping the ZIP code from Yahoo geocoder

[raykon]

Hi. I built a little Excel VBA program to capture information from the Yahoo geocoder, for which I have a license. But for some reason, it'll return all of the information that the Yahoo geocoder provides, except for the ZIP code, which in my case, happens to be the only thing I need.

My Perl script, on the other hand, can capture the ZIP code information too, but not my VBA, and long story short, I need to be able to do this Excel VBA. Can someone rise above my VBA limitations and provide me with a simple Excel VBA program to download the ZIP code information, for a single address, using the Yahoo geocoder?

If you can, thanks in advance!

- raykon

[mvidas]

Hi raykon,

Sample response
The following is a sample response for the geocode of the Yahoo! headquarters:

<?xml version="1.0" encoding="UTF-8"?>
<ResultSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="urn:yahoo:maps"
xsi:schemaLocation="urn:yahoo:maps http://api.local.yahoo.com/MapsService/V1/GeocodeResponse.xsd">
<Result precision="address">
<Latitude>37.416384</Latitude>
<Longitude>-122.024853</Longitude>
<Address>701 FIRST AVE</Address>
<City>SUNNYVALE</City>
<State>CA</State>
<Zip>94089-1019</Zip>
<Country>US</Country>
</Result>
</ResultSet>

Is this the response you're having trouble getting the zip from?
Can you post what you're doing currently (removing your license ID or whatever from the code)?
Just a thought, are you only looking for 5 digits maybe? How are you retrieving the response anyways?

[raykon]

Yes, that's exactly what I'm trying to do. And in my case it doesn't matter if the ZIP is 5 digits or ZIP+4. Here's how I am trying it (I had to insert spaces in the two "http" references below in order to be permitted to post this, because I don't have the required number of posts to be able to include links):

[vba]Sub GetMeZIPcode()
Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:="URL;h t t p://api.local.yahoo.com/MapsService/V1/geocode?" & _
"appid=MyYahooID&location=516%20E%20%Pasadena%20Ave,%20Phoenix", _
Destination:=shFirstQtr.Cells(1, 1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh
End With
End Sub[/vba]


But the result that I get, which is shown below (after a bit of transposing), doesn't include the ZIP, and for the life of me, I just can't figure out why. Perhaps the ZIP code information was overwritten?

(Results are transposed)
/ResultSet
/@xsi:schemaLocation urn:yahoo:maps h t t p: //api.local.yahoo.com/MapsService/V1/GeocodeResponse.xsd
/Result/@precision address
/Result/Address 516 E PASADENA AVE
/Result/City PHOENIX
/Result/Country US
/Result/Latitude 33.510418
/Result/Latitude/#agg 33.510418
/Result/Longitude -112.067207
/Result/Longitude/#agg -112.067207

Thanks!

[mvidas]

Didn't realize there was a post minimum to post links.. either way it is probably better this way (once google caches this page, your link would be explored by google, using up at least one of your allowed queries).

Though I edited yours for you, [uvba]a[/uvba]

In all honesty, I think perhaps your transposing code is losing it. When I run your code above as-is, I get the following put into A2:

<ResultSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:yahoo:maps" xsi:schemaLocation="urn:yahoo:maps <A href="http://api.local.yahoo.com/MapsService/V1/GeocodeResponse.xsd"><Result">
http://api.local.yahoo.com/MapsService/V1/GeocodeResponse.xsd"><Result precision="address"><Latitude>33.510418</Latitude><Longitude>-112.067207</Longitude><Address>516 E PASADENA AVE</Address><City>PHOENIX</City><State>AZ</State><Zip>85012-1519</Zip><Country>US</Country></Result></ResultSet>

If the zip is really all you're after, you could just look for <Zip> in that cell, like[vba] If InStr(shFirstQtr.Range("A2"), "<Zip>") > 0 Then
With shFirstQtr
MsgBox "Zip code is: " & Mid(.Range("A2"), InStr(.Range("A2"), "<Zip>") _
+ 5, InStr(.Range("A2"), "</Zip>") - InStr(.Range("A2"), "<Zip>") - 5)
End With
End If[/vba]

I would like to ask though.. is there anything you're doing with that sheet other than returning the query table? If not, you can retrieve this same info from VBA itself, without having to use a worksheet/querytable/web query. Though there are a few ways of retrieving a website via vb, here is a simple one:
[vba]Sub GetMeZIPcode()
Dim tStr As String
tStr = GetWebText("http://api.local.yahoo.com/MapsServi...Ave,%20Phoenix")
If InStr(tStr, "<Zip>") > 0 Then
MsgBox "Zip code is: " & Mid(tStr, InStr(tStr, "<Zip>") + 5, _
InStr(tStr, "</Zip>") - InStr(tStr, "<Zip>") - 5)
End If
End Sub
Function GetWebText(ByVal vWebSite As String) As String
Dim oXMLHTTP As Object, vWebText As String, i As Long
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebSite, False
oXMLHTTP.Send
If (oXMLHTTP.readyState = 4) And (oXMLHTTP.Status = 200) Then
vWebText = oXMLHTTP.ResponseText
vWebText = Replace(vWebText, "&quot;", Chr(34))
vWebText = Replace(vWebText, "&lt;", Chr(60))
vWebText = Replace(vWebText, "&gt;", Chr(62))
vWebText = Replace(vWebText, "&amp;", Chr(38))
vWebText = Replace(vWebText, "&nbsp;", Chr(32))
For i = 1 To 255
vWebText = Replace(vWebText, "&#" & i & ";", Chr(i))
Next
End If
GetWebText = vWebText
Set oXMLHTTP = Nothing
End Function[/vba]

Matt

[stanl]

Probably off-topic, but

oXMLHTTP.ResponseText

... why not go with

oXMLHTTP.ResponseXML.xml .... then use xPath? Stan

[mvidas]

Because I didnt know you could do that

[raykon]

Thanks, MVIDAS. I was able to plug your coding into my program and get everything to work flawlessly. I've got a lot to learn, but the program is working now, and I really appreciate the help.

[stanl]

Because I didnt know you could do that

I was actually off-track.
In this instance since Yahoo uses REST, responsetext and responseXML.xml are the same. You can then use the DOM Parse

oXML = CreateObject("MSXML2.DOMDocument.4.0")
oXML.async = False
oXML.LoadXML(oHTTP.ResponseText)

but you cannot use regular xPath such as SelectSingleNode("Zip"); instead iterate oXML.documentElement.GetElementsByTagName("*"), the nodes look like the attached. Understanding this has helped me in another area and I am happy raykon is happy. Stan

[mvidas]

Thanks, Stan,

I learned a couple things this morning:
-I wish .childNodes let you put node names instead of numbers
-This can be done easily with xml!
If oXML contains the loaded xml:[vba]oXML.childNodes(1).childNodes(0).childNodes(5).Text[/vba]That will return the zip (the 5 being the zip code node)
As a function:[vba]Function GetTheZip(ByVal vURL As String) As String
Dim oXML As Object
Set oXML = CreateObject("msxml2.domdocument")
oXML.async = False
If oXML.Load(vURL) Then GetTheZip = oXML.childNodes(1).childNodes(0).childNodes(5).Text
Set oXML = Nothing
End Function[/vba]
Used by:[vba]Sub ItWorks()
MsgBox GetTheZip("http://api.local.yahoo.com/MapsService/V1/geocode" & _
"?appid=MyYahooID&location=516%20E%20%Pasadena%20Ave,%20Phoenix")
End Sub[/vba]

By the way, raykon, if this issue is closed for you, you can go to Thread Tools at the top of the page and select "Mark Thread Solved". This will close this out so the forum helpers will know you don't need anymore help with this. Also, feel free to post a couple times if you'd like to get that post count higher (so you can post a link).

Matt

[stanl]

I swear I placed 2 more posts on this thread, one of which explained an xPath workaround for SelectSingleNode(). Stan

[mvidas]

I guess I assumed you would automatically be subscribed to http://www.vbaexpress.com/forum/showthread.php?t=10215

[stanl]

got it and did reply... thanx