francislam
asked on
Get External Web Result into Excel
I am working on an Excel model which need to look for the state, city and county name from an external web site.
Taking the web site "www.geonames.org" for example. Is it possible for me to input the zip code and country in two cells in Excel and then it will return the searched result of "Name" and "Country" back to Excel?
Francis
Taking the web site "www.geonames.org" for example. Is it possible for me to input the zip code and country in two cells in Excel and then it will return the searched result of "Name" and "Country" back to Excel?
Francis
Sure this is possible, there are really 2 approaches:
1. Use a web service.
2. Use some form of IE automation to request the page, parse the html returned to extract your results.
The first approach is the best as it is specifically designed to do what you want but is reliant on the website having a web service available for you to use. The second is no more difficult to impliment but can be easily broken if the website updates their web pages.
See: http://www.excely.com/excel-vba/ie-automation.shtml
1. Use a web service.
2. Use some form of IE automation to request the page, parse the html returned to extract your results.
The first approach is the best as it is specifically designed to do what you want but is reliant on the website having a web service available for you to use. The second is no more difficult to impliment but can be easily broken if the website updates their web pages.
See: http://www.excely.com/excel-vba/ie-automation.shtml
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ASKER
I assumed you wanted to input country and zipcode. "Las Vegas" is not a zipcode.
Your question was about pulling in data from a website. I uploaded a code example that demonstrates a technique to do this. Now it is up to you to adapt this code to your specific needs.
Your question was about pulling in data from a website. I uploaded a code example that demonstrates a technique to do this. Now it is up to you to adapt this code to your specific needs.
ASKER
Sure, you could link the cell representing the country and the cell representing the name.
The problem with this web page is that the results for different searches are not represented in the same way.
For some (US) results, the country is placed in cell C6 while for other searches is resides in G5.
So to be safe, i'd suggest parsing the web results with a macro, more or less as Nicobo does.
It could be performed however with less and more understandable code like this :
web-query2.xlsm
The problem with this web page is that the results for different searches are not represented in the same way.
For some (US) results, the country is placed in cell C6 while for other searches is resides in G5.
So to be safe, i'd suggest parsing the web results with a macro, more or less as Nicobo does.
It could be performed however with less and more understandable code like this :
web-query2.xlsm
I'm using this page to get the result:
http://www.geonames.org/postalcode-search.html?q=89101&country=US
because it has a clearer structure and thus is easier to process.
I saw the site has a webservice also:
http://www.geonames.org/export/web-services.html
The information retrieved from those pages is probably even easier to process.
http://www.geonames.org/postalcode-search.html?q=89101&country=US
because it has a clearer structure and thus is easier to process.
I saw the site has a webservice also:
http://www.geonames.org/export/web-services.html
The information retrieved from those pages is probably even easier to process.
or more reliable :
Sub websearch()
Dim ie As Object
Application.StatusBar = "Initialising"
Set ie = CreateObject("internetexplorer.application")
Application.StatusBar = "Processing web query"
ie.navigate "http://www.geonames.org/search.html?q=" & Range("B1") & "&country=" & Range("B2")
Do While ie.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Application.StatusBar = "Parsing results"
For Each element In ie.document.getelementsbytagname("table")
If element.classname = "restable" Then
pos_name = -1
pos_countru = -1
'-- determine result fields
For x = 0 To element.Children.Length - 1
For y = 0 To element.Children(x).Children.Length - 1
For Z = 0 To element.Children(x).Children(y).Children.Length - 1
Debug.Print x & "." & y & "." & Z & " => " & element.Children(x).Children(y).Children(Z).innerhtml
If element.Children(x).Children(y).Children(Z).innertext = "Name" Or element.Children(x).Children(y).Children(Z).innertext = "Place" Then
name_x = x
name_y = y
name_z = Z
ElseIf element.Children(x).Children(y).Children(Z).innertext = "Country" Then
country_x = x
country_y = y
country_z = Z
End If
Next Z
Next y
If name_x > 0 And country_x > 0 Then Exit For
Next x
'-- determine result values
If element.Children(name_x).Children(name_y + 1).Children(name_z).Children.Length > 0 Then
Range("B4") = element.Children(name_x).Children(name_y + 1).Children(name_z).FirstChild.innertext
Else
Range("B4") = element.Children(name_x).Children(name_y + 1).Children(name_z).innertext
End If
If element.Children(country_x).Children(country_y + 1).Children(country_z).Children.Length > 0 Then
Range("B5") = element.Children(country_x).Children(country_y + 1).Children(country_z).FirstChild.innertext
Else
Range("B5") = element.Children(country_x).Children(country_y + 1).Children(country_z).innertext
End If
End If
Next element
If Not ie.Visible Then
ie.Quit
Set ie = Nothing
End If
End Sub
ASKER
ASKER
Hi akoster,
Any way to show also "Nevada" and "Clark County" in your search result? Either in one cell or in different cells or rows will be fine.
Any way to show also "Nevada" and "Clark County" in your search result? Either in one cell or in different cells or rows will be fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As Nicobo is the first one who suggest parsing the web results with a macro, I have allocated 100 points to him. Hope akoster is OK with this.
excellent !
When you enter a country and postal on rows 2 or 3 the place will show up in column C.
Geo.xlsm