SenthilKumar Balakrishnan February 2016

Excel VBA: Fetch Google Map latitude, longitude by clicking

This I have done: I have to show the google map in Excel or Browser by using a address or location or latitude and longitude by showing it in IE or default browser and I tried in MS web browser component in excel but that is opening in compatibility mode. I tried with Microsoft power map that required some installation.

I need help on: from the opened map I need to select location/address I need the latitude and longitude value to be update in my excel sheet.

In addition to that simple way, I need to send latitude and longitude to google map and same time I need receive latitude and longitude from map by clicking the map. in excel web browser no scripts are running.

Answers


JNevill February 2016

To solve this you'll have to overcome two obstacles:

  1. Find an Event on the WebBrowser control that fires when the browser is loaded with a new URL.
  2. Parse the Lat and Long from the URL

Thankfully, Google's newer Map's URL are super friendly for parsing... AND Microsoft's WebBrowser control has some nice hooks built in.

You probably have some code that looks something like:

Sub webbrowsergo()
    WebBrowser1.Navigate2 ("https://www.google.com/maps/@39.1240533,-79.8562553,16z")
End Sub

That will send the web browser control to somewhere in West Virginia, I think.

Now, in your WorkSheet's VBA code, you can use the WebBrowser_NavigateComplete2 event to hook in. This event will fire once a URL has completely loaded. Like if anyone moves the map or changes the zoom.

In your Worksheet's VBA code, hit the first drop down box at the top of the VBE and choose your WebBrowser control (probably named WebBrowser1 or something). Then in the second drop down box, find the NavigateComplete2 event.

It looks something like:

enter image description here

Your VBE will make a new sub for you and you can add the following:

Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant)
    Debug.Print "Navigation complete, URL=" & URL

    Dim strLat As String, strLong As String

    strLat = Split(URL, "@")(1)
    strLat = Split(strLat, ",")(0)

    strLong = Split(URL, ",")(1)

    Debug.Print strLat, strLong        
End Sub

So, pretty simple stuff. We use the Navigate2Complete event of the WebBrowser control which fires every time the WebControl updates, and has this nice parameter called URL which contains the URL t

Post Status

Asked in February 2016
Viewed 3,809 times
Voted 10
Answered 1 times

Search




Leave an answer