I’ve been writing a few bits of code lately which have required using Google tools. I thought I’d write up some notes here for my information than anything else but may come in handy for others too. This post is a nifty tool for tracking the distance travelled by building materials from source to site. Used in conjunction with the carbon factors per tonne km from Defra, this allows a decent estimate for the transport stage of a lifecycle carbon footprint assessment.
The custom function for Excel works by entering two place names – or postcodes – and finding the shortest route between them using Gmaps route planner. I’ve called the UDF
G_DISTANCE and the syntax for using it is
=G_DISTANCE(Origin, Destination). It returns a value of the distance between the two locations in
miles kilometres. If any kind of error occurs then the function returns 0.
This table shows the various ways you can use the function. You can also mix and match the types of input parameter you use. For example you may the site location precisely – either by latitude and longitude or postcode, but only know the city or even the country of origin of the materials. That’s fine – the function should still give you the right answer, or at worst return zero.
Two note of warning:
1) The Gmaps terms of service state that you are only allowed to use the API when displaying a map, so using this without doing so is at your own risk (hence the map above…).
2) Gmaps will generally give you a road-based route which may not be the most appropriate. For example cork from Portugal would probably be shipped rather than delivered all the way by HGV. If anyone has a better function for these cases please get in touch.
Function G_DISTANCE(Origin As String, Destination As String) As Double ' Requires a reference to Microsoft XML, v6.0 ' Draws on the stackoverflow answer at bit.ly/parseXML Dim myRequest As XMLHTTP60 Dim myDomDoc As DOMDocument60 Dim distanceNode As IXMLDOMNode G_DISTANCE = 0 ' Check and clean inputs On Error GoTo exitRoute Origin = Replace(Origin, " ", "%20") Destination = Replace(Destination, " ", "%20") ' Read the XML data from the Google Maps API Set myRequest = New XMLHTTP60 myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _ & Origin & "&destination=" & Destination & "&sensor=false", False myRequest.send ' Make the XML readable usign XPath Set myDomDoc = New DOMDocument60 myDomDoc.LoadXML myRequest.responseText ' Get the distance node value Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value") If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000 exitRoute: ' Tidy up Set distanceNode = Nothing Set myDomDoc = Nothing Set myRequest = Nothing End Function
As ever, you need to install this as a code module in the workbook you want to use it in. It also requires that you set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).