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:
Then I will try to extract that value using
and parsing that value for the data I want. However, when the code hits
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 the http request Set xHttp = New MSXML2.XMLHTTP "GET", sURL & sLocData xHttp.send until it done Do DoEvents Loop Until xHttp.readyState = 4 the returned data sResponse = xHttp.responseText 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") sTest = "[ 103.9041520, 1.3222160, 0 ]" End Sub
source to share