Returning GPS Coordinates, and calculating Driving distance in Excel 2007

I recently had the mispleasure of having to code a vbscript to allow me to take advantage of the Google Distance Matrix API and the GPS encoding function of Maps. Luckily, I found a wonderful article on the Police Analyst blog. One part of the chore was completed! It even walked me through how to use the VB script editor for the purpose.

Utilizing my programming prowess (which is pretty poor) and some amazingly smart coder friends, we hammered out how to get a return on the Driving Matrix API as well. Two new functions were born in Excel: GoogleDistance and GoogleGeocode.

Use is pretty simple. In the cell, place your formula like this:

=GoogleGeocode(CELL)

The CELL is the reference cell for where you want the gps coordinate for. It will do specific addresses, or cities.

=GoogleDistance(CELL1, Cell2)

CELL1 is the originating location. C ELL2 is the destination. You can use GPS coordinate, address, or city for either.

To implement the functions in your Excel 2007, just follow the directions at the Police Analyst post, get your own Google Maps Distance Matrix API and use the following cut and paste instead of the Police Analyst one:

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 = “https://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
Function GoogleDistance(origin As String, destination As String) As String
Dim strAPIKey As String
Dim strOrigin As String
Dim strDestination As String
Dim strQuery As String

‘Set API Key
strAPIKey = “<INSERT YOUR API KEY HERE>”

‘URL Encode Origin and Destination
strOrigin = URLEncode(origin)
strDestination = URLEncode(destination)

‘Assemble the query string
strQuery = “https://maps.googleapis.com/maps/api/distancematrix/xml?”
strQuery = strQuery & “origins=” & strOrigin
strQuery = strQuery & “&destinations=” & strDestination
strQuery = strQuery & “&key=” & strAPIKey

‘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)

Debug.Print googleService.responseText

Set oNodes = googleResult.getElementsByTagName(“distance”)

If oNodes.Length = 1 Then
For Each oNode In oNodes
GoogleDistance = oNode.ChildNodes(0).Text
Next oNode
Else
GoogleDistance = googleResult.ChildNodes(1).Text

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

You can also download the code as a text file from here: Google GPS conversion and Distance


Comments are closed.