Excel VBA: Fetch Google Map latitude, longitude by clicking the map

[senthilkumarsb]

This I have tried 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 - 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.

 

[michaelsmith559]

Here is a code I found online that will return lat/long coordinates if you know a city or address. This is not my code, I think it came from myengineeringworld.com You use as follows:
Put a city, state or address into a cell: example, in B16 put Boston, MA. In cell C16 put =GoogleGeocode(B16).

Function GoogleGeocode(address As String) As String
  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String

  strAddress = URLEncode(address)

  'Assemble the query string
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
  strQuery = strQuery & "address=" & strAddress
  strQuery = strQuery & "&sensor=false"

  'define XML and HTTP components
  Dim googleResult As New MSXML2.DOMDocument
  Dim googleService As New MSXML2.XMLHTTP
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode

  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation

  googleService.Open "GET", strQuery, False
  googleService.send
  googleResult.LoadXML (googleService.responseText)

  Set oNodes = googleResult.getElementsByTagName("geometry")

  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      GoogleGeocode = strLatitude & "," & strLongitude
    Next oNode
  Else
    GoogleGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
End Function


Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
  Dim StringLen As Long: StringLen = Len(StringVal)
  
  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String
    
    If SpaceAsPlus Then Space = "+" Else Space = "%20"
    
    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
        
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = Space
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

 

[michaelsmith559]

Here is a screenshot:

Excel 2012
	B	C
15		LAT/LONG
16	BOSTON,MA	42.3600825,-71.0588801
Sheet1

Cell Formulas
Range	Formula
C16	=GoogleGeocode(B16)

 

[michaelsmith559]

Here is a screenshot if you need lat/long in separate cells:

Excel 2012
	B	C	D	E
15		LAT/LONG	LAT	LONG
16	BOSTON,MA	42.3600825,-71.0588801	42.3600825	-71.0588801
Sheet1

Cell Formulas
Range	Formula
C16	=GoogleGeocode(B16)
D16	=LEFT(C16,SEARCH(",",C16)-1)
E16	=MID(C16,SEARCH(",",C16)+1,20)