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.
To solve this you'll have to overcome two obstacles:
Find an Event on the WebBrowser control that fires when the browser
is loaded with a new URL.
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:
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:
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
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
Asked in February 2016Viewed 3,809 timesVoted 10Answered 1 times