#GOOGLE MAPS API VBA EXCEL HOW TO#
#GOOGLE MAPS API VBA EXCEL CODE#
#GOOGLE MAPS API VBA EXCEL PLUS#
#GOOGLE MAPS API VBA EXCEL FREE#

This API is really easy to use compared to Bing maps (as we need to make just one call). LetРІР‚в„ўs say your Google Maps API key is in a cell named gmaps.key Distance & travel time in Excel РІР‚вЂњ Google Maps API Please read the Bing maps developer documentation for more. too by using extra parameters in the lookup URL. The default output values are in KM for distance and minutes for duration.

This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation. The output for this is an XML that looks like:Р”В¬opyright Р’В© 2018 Microsoft and its suppliers. $1&destinations= $2&travelMode= $3&o=xml&key= bingmaps.keyР”Р‡or example, the distance lookup URL for above addresses is: Once we have lat & long values for both origin and destination, we can call distance lookup API to calculate distance, travel time values. Step 2: Calculate distance between two geolocations

#GOOGLE MAPS API VBA EXCEL CODE#

You can use FILTERXML to test the status code for the response or other interesting bits too. How does FILTERXML() work?Р’В It takes the XML value in C21 and finds the first Latitude tag (hence ) anywhere (hence //) This will give us both lat & long values. LetРІР‚в„ўs say the output of WEBSERVICE is in cell C21. 200OKValidCredentials_REMOVED_1Phillip St, Johnsonville, Wellington 6037, New Zealand -41.22292174.80164-41.2241799174.80136-41.22166174.80196RoadBlockPhillip StWellingtonWellington CityNew ZealandPhillip St, Johnsonville, Wellington 6037, New ZealandWellington6037HighGood-41.22292174.80164InterpolationDisplay-41.22292174.80164InterpolationRouteР”Р‡rom this XML, we need to extract the LAT & LONG values highlighted in blue. When you call this URL using WEBSERVICE(), you will get an XML output (as our output parameter is o=xml, if you omit this, you will get json).Р”В¬opyright Р’В© 2018 Microsoft and its suppliers.

Notice all $ symbols? Use SUBSTITUTE to replace them with actual location values. $1&adminDistrict= $2&locality= $3&postalCode= $4&addressLine= $5&maxResults=1&o= xml&key= bingmaps.key So we will use point lookup API to convert address to geolocation (lat&long). Step 1: Fetch Latitude and Longitude for the addressesР”В«efore calculating the distance, we need to know where on earth our addresses are. For Bing maps API, we need address to be broken in to below pieces. Start by creating a range of cells to capture origin & destination addresses. In this demo, we focus on calculating distance & travel time between one set of points, but you can use the ideas to calculate distance matrix for a range of points.Р’В For example, you can calculate travel time between all your warehouses and customer locations easily. LetРІР‚в„ўs say you have the api key in a cell named bingmaps.key Using Excel to calculate distance & travel time between two points РІР‚вЂњ Bing Maps APIР”Р„s the process for getting Bing Maps API key is easy, letРІР‚в„ўs assume that is what you have. Watch it below (or on our YouTube channel). I made a small video explaining the process.

#GOOGLE MAPS API VBA EXCEL PLUS#

Plus for Google Maps API, you need to provide your credit card details (according to Google, you will not be billed automatically though).

The API key process is somewhat technical and can be confusing.

#GOOGLE MAPS API VBA EXCEL HOW TO#

How to get the API Key from Google Maps or Bing Maps:

Excel 2013 or above (we will be using WEBSERVICE() and FILTERXML() functions in Excel).

#GOOGLE MAPS API VBA EXCEL FREE#

Free maps API from either Google Maps or Bing Maps.But what if you are building some model (or calculator) and want to find out the distance, travel time, address points (latitude, longitude) and may be even distance matrix (given two sets of points, all distances between them)? We can use the public APIs from Bing maps or Google Maps to get the answer to our spreadsheet. If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. Ever wanted to calculate distance using Excel РІР‚вЂњ between two locations (physical addresses)?

Google maps api vba excel