How to use appIE.Document.Body.innerHTML
So I am trying to get the latitude and longitude of a given postal code and I am trying to use VBA to put this in an excel worksheet. My code looks like this:
Private appIE As Object
Function GeoCode(sLocationData As String) As String
'//Dont want to open and close all day long - make once use many
If appIE Is Nothing Then
CreateIEApp
'// Creates a new IE App
'// if = nothing now then there was an error
If appIE Is Nothing Then
GeoCode = "Sorry could not launch IE"
Exit Function
Else
'// do nothing
End If
Else
'// do nothing
End If
'//clearing up input data
'sLocationData = Replace(sLocationData, ",", " ")
sLocationData = Replace(sLocationData, " ", "+")
sLocationData = Trim(sLocationData)
'//Build URL for Query
sLocationData = "http://maps.google.com/maps/geo?q=%20_" & sLocationData
'// go to the google web service and get the raw CSV data
'// CAUSES PROBLEM AS SPECIFIED BELOW
appIE.Navigate sLocationData
Do While appIE.Busy
Application.StatusBar = "Contacting Google Maps API..."
Loop
Application.StatusBar = False
On Error Resume Next
'// Parsing
GeoCode = appIE.Document.Body.innerHTML
GeoCode = Mid(GeoCode, InStr(GeoCode, ",") + 1, InStr(GeoCode, "/") - InStr(GeoCode, ",") - 2)
appIE = Nothing
End Function
Then the Google Maps API returns a JSON formatted value according to this link:
http://maps.google.com/maps/geo?q=%20_400012
Then I will try to extract that value using
appIE.Document.Body.innerHTML
,
and parsing that value for the data I want. However, when the code hits appIE.Navigate sLocationData
,
I am prompted to save the file as "geo". When saved and opened as a .txt file, I get the same JSON formatted value, but I need the values in my sheet.
Is there a way to do this?
Thanks in advance!
source to share
This link did not work for me in Firefox - 610 answer. If I remove the space and underscore it works. I don't know why IE wants to load, maybe some settings that tell it to always load JSON and not display it. In any case, consider using an MSXML HTTP request rather than IE automation.
Install a link to Microsoft XML, v6.0 or similar (VBE - Tools - Links).
Function GeoCode(sLocData As String) As String
Dim xHttp As MSXML2.XMLHTTP
Dim sResponse As String
Dim lStart As Long, lEnd As Long
Const sURL As String = "http://maps.google.com/maps/geo?q="
Const sCOOR As String = "coordinates"": " 'substring that we'll look for later
'send the http request
Set xHttp = New MSXML2.XMLHTTP
xHttp.Open "GET", sURL & sLocData
xHttp.send
'wait until it done
Do
DoEvents
Loop Until xHttp.readyState = 4
'get the returned data
sResponse = xHttp.responseText
'find the starting and ending points of the substring
lStart = InStr(1, sResponse, sCOOR)
lEnd = InStr(lStart, sResponse, "]")
GeoCode = Mid$(sResponse, lStart + Len(sCOOR), lEnd - lStart - Len(sCOOR) + 1)
End Function
Sub Test()
Dim sTest As String
sTest = GeoCode("400012")
Debug.Assert sTest = "[ 103.9041520, 1.3222160, 0 ]"
End Sub
source to share