Smartphone app excel

Google Geocoding API in Excel (I)

Reading a post about how geolocation can be used as electronic evidence, a task that I recently had the good fortune to start came to my mind, perhaps not very relevant, but quite attractive, since, even if in a second phase it was somewhat automatic, it is always nice to do work where you end up with new ideas and/or knowledge.

The objective of the task was to obtain from the addresses of an Excel document (complete addresses, names of countries, communities, provinces or municipalities…) their coordinates on the map. This transformation is known as Geolocation and although at first you might think that it would be something complicated or expensive to obtain, the truth is that it is quite fast and effective results.

All this thanks to the Google API for Geolocation, with which we get that, making a query in which we send a URL with the data of the address we want, we get an XML with all the information of that location, including what we needed: latitude and longitude.

If we click on the following link we will see that we obtain the geolocation of the office where I am 馃檪

http://maps.googleapis.com/maps/api/geocode/xml?address=Glorieta%20Fernando%20Qui%C3%B1ones&sensor=false

Next, I am going to explain the steps that we must take to obtain our document with all the necessary coordinates.

To do this, first indicate that we will use the 2010 version of Excel.

We will need to have the “Scheduler” tab visible since this is where we are going to start the geolocation process.

To enable this tab, go to the File menu, Options.

Click on “Customize ribbon”, select “Main tabs” in the drop-down menu under the heading of the same name, select the “Scheduler” checkbox and click on the “OK” button.

Once these steps have been completed, we can see the Scheduler option as another tab.

We now need to run the VisualBasic command in Excel to create our function.

Therefore we click on the icon that gives us this option:

A new screen opens.

Select “Insert” and then “Module“.

And we copy the following code in the module that has been created.

On the internet there are many variants of code to get what we want. I used this one and I can say that it worked quite well.

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

The code makes use of the Microsoft XML library so we will have to add a reference to it. To do this we click on the “Tools” menu option and select “References”.

A list of checkboxes will appear. We need to find “Microsoft XML v6.0” in the list and check that it works. Click “OK“.

Save the document as “Excel workbook enabled for macros”.

If we have done all the steps correctly we should be able to use the newly created functions as we would use the default Excel loaded functions.

That is, by standing in any cell and typing the following:

=GoogleGeocode(A646)

The code above serves as an example.

Of course, in this geolocation process I encountered some problems, some perfectly avoidable and others with which we can do absolutely nothing, at least as far as I know. But that will be a matter for another day, since the post is getting a little long already :).

 

Contact

    La mejor soluci贸n de firma electr贸nica para tu empresa

    Scroll to Top