Excel VBA: Fetch Google Map latitude, longitude by clicking -

this have done: have show google map in excel or browser using address or location or latitude , longitude showing in ie or default browser , tried in ms web browser component in excel opening in compatibility mode. tried microsoft power map required installation.

i need on: opened map need select location/address need latitude , longitude value update in excel sheet.

in addition simple way, need send latitude , longitude google map , same time need receive latitude , longitude map clicking map. in excel web browser no scripts running.

to solve you'll have overcome 2 obstacles:

1. find event on webbrowser control fires when browser loaded new url.
2. parse lat , long url

thankfully, google's newer map's url super friendly parsing... , microsoft's webbrowser control has nice hooks built in.

you have code looks like:

sub webbrowsergo()     webbrowser1.navigate2 ("https://www.google.com/maps/@39.1240533,-79.8562553,16z") end sub 

that send web browser control somewhere in west virginia, think.

now, in worksheet's vba code, can use webbrowser_navigatecomplete2 event hook in. event fire once url has loaded. if moves map or changes zoom.

in worksheet's vba code, hit first drop down box @ top of vbe , choose webbrowser control (probably named webbrowser1 or something). in second drop down box, find navigatecomplete2 event.

it looks like:

your vbe make new sub , can add following:

private sub webbrowser1_navigatecomplete2(byval pdisp object, url variant)     debug.print "navigation complete, url=" & url      dim strlat string, strlong string      strlat = split(url, "@")(1)     strlat = split(strlat, ",")(0)      strlong = split(url, ",")(1)      debug.print strlat, strlong         end sub 

so, pretty simple stuff. use navigate2complete event of webbrowser control fires every time webcontrol updates, , has nice parameter called url contains url loaded webbrowser control. use split() parse , grab lat , long.

debug.print writes out variables vbe's immediate window. go view>>immediate window turn on if don't have already. obviously, can change push lat , long worksheet or something.