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
    '// 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
        '// do nothing
    End If
    '// do nothing
End If
'//clearing up input data
'sLocationData = Replace(sLocationData, ",", " ")
sLocationData = Replace(sLocationData, " ", "+")
sLocationData = Trim(sLocationData)

'//Build URL for Query
sLocationData = "" & sLocationData

'// go to the google web service and get the raw CSV data
appIE.Navigate sLocationData

Do While appIE.Busy
    Application.StatusBar = "Contacting Google Maps API..."

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 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

1 answer

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 = ""
    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

    'wait until it done
    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




All Articles