![bing map api vba bing map api vba](https://blog.atwork.at/image.axd?picture=image_3801.png)
If you would like to pull a list from the web or local JSON file, check out Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query. All you need now is a list of addresses to use it with. That’s it! You should now have a working travel time function. This can be done in Excel or by modifying the functions in VBA. Note: The Google Maps Directions API always returns distances in meters. Convert to kilometers with this: =ROUND(A9/1000, 1)&" km". Try the following: =TRAVELDISTANCE("24 Sussex Drive Ottawa ON", "Parliament Hill", A1). This prints a helpful “7 minutes 15 seconds” for the 24 Sussex example. If you would like this to be displayed in minutes and seconds, try this function: =FLOOR.MATH(A8/60)&" minutes "&MOD(A8, 60)&" seconds" where A8 is the cell with the travel time in seconds. This returns a travel time of about 435 seconds. Place your API key in cell A1, then try the following: =TRAVELTIME("24 Sussex Drive Ottawa ON", "Parliament Hill", A1). You should now be able to use the functions from within Excel.
![bing map api vba bing map api vba](https://i0.wp.com/bvisual.net/wp-content/uploads/2010/03/image_thumb5b25d7cee882e.png)
Meters = meters + leg("distance")("value") Seconds = seconds + leg("duration")("value")įunction TRAVELDISTANCE(origin, destination, apikey) Set parsed = JsonConverter.ParseJson(response)įor Each leg In parsed("routes")(1)("legs") Set httpReq = CreateObject("MSXML2.XMLHTTP") StrUrl = "" & origin & "&destination=" & destination & "&key=" & apikey ' Returns the number of seconds it would take to get from one place to anotherįunction TRAVELTIME(origin, destination, apikey) To insert the code, create a new module with Insert > Module. The default mode is driving, but refer to the Directions API documentation for information on other modes and adjust the strURL variable accordingly. Note that the request may return multiple routes, but then function simply returns the time of the first route. The response of the web request is then parsed by VBA-JSON and the relevant variable returned. It is simply takes the three parameters and formats them into a web request.
#BING MAP API VBA CODE#
With the references configured, we can now write the code for the function. Please make sure the pictured references are selected. More details can be found at the bottom of the project homepage.
#BING MAP API VBA INSTALL#
If you require support for Excel for Mac, you will need to install VBA-Dictionary from the author of VBA-JSON. Go to Tools > References... In addition to the references already selected, check off “Microsoft Scripting Runtime” (for Dictionary support needed by VBA-JSON) and “Microsoft WinHTTP Services, version 5.1” (to make the HTTP request to the API). Next, make sure the appropriate references are enabled. Import JsonConverter.bas using the Import File... function. A JsonConverter module will appear in the sidebar. In order to import the VBA-JSON file, go to File > Import File… (Ctrl + M). Right click the ribbon, go to “Customize the Ribbon…”, and check Developer under Main tabs. If you do not have this button, ensure the “Developer” tab is enabled. Open the Visual Basic Editor (Alt + F11).
#BING MAP API VBA DOWNLOAD#
You can download the latest version from here. Step 1: Create a new macro file and add VBA-JSONīecause the Google Maps Directions API is a JSON API, we will use VBA-JSON to make it easy to use the results from the web request. Create a new project and make sure the Directions API is added. The “Getting Started” page can help you with this.
![bing map api vba bing map api vba](https://pbs.twimg.com/profile_images/688047615926087680/m33TW6zZ_400x400.png)
In order to use the function, an API key is required. The origin and destination will be strings, and can be either an exact address or the name of a place. The format of the function will be as follows: =TRAVELTIME(origin, destination, api_key), =TRAVELDISTANCE(origin, destination, apikey).
![bing map api vba bing map api vba](https://img.youtube.com/vi/zvOvkYiicf8/hqdefault.jpg)
This will allow you to get the travel time between the two locations. In this article, we will create an Excel function to calculate the distance between two addresses using the Google Maps directions API.