I've structured my data as the from address being in the first column of the range, and the destination address being in the column immediately after that.
#Google maps api vba excel code
I've also changed this code to a sub, as I'd need to iterate over some sort of collection.
![google maps api vba excel google maps api vba excel](https://cdn.cupdf.com/img/1200x630/reader020/image/20190704/55ab25881a28ab3f608b470b.png)
What I've done below is limited the pull to a single request, and made the code asynchronous. Also, as others have pointed out what's killing performance is the synchronous code. Any thoughts on speeding this up?ĭepending on what you want to return you can actually leverage just one API method from Bing to retrieve the data you are after, the Driving Route API. To be clear, the process works well, just extremely slowly. GetLatLong = WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Latitude") & "," & WorksheetFunction.FilterXML(objHTTP.responseText, "//Point//Longitude")
#Google maps api vba excel zip
Url = firstVal & state & secondVal & city & thirdVal & zip & fourthVal & address & lastVal Public Function GetLatLong(address As String, city As String, state As String, zip As String)ĭim firstVal As String, secondVal As String, thirdVal As String, fourthVal As String, lastVal As StringįirstVal = " States of America&adminDistrict=" GetTime = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDuration"), 0) & " minutes" Public Function GetTime(start As String, dest As String) GetDistance = Round(WorksheetFunction.FilterXML(objHTTP.responseText, "//TravelDistance"), 0) & " miles"
#Google maps api vba excel windows
tRequestHeader "User-Agent", "Mozilla/4.0 (compatible MSIE 6.0 Windows NT 5.0)" Url = firstVal & start & secondVal & dest & lastVal Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") LastVal = "&travelMode=driving&o=xml&key=&distanceUnit=mi" The code pieces in use are: Public Function GetDistance(start As String, dest As String)ĭim firstVal As String, secondVal As String, lastVal As String This is an amalgamation of multiple different processes found through Google searches. I am not a coder, but I can functionally modify found code to my purposes. The employee population is approximately 2300, and it takes almost an hour to execute. The process is working, but it is excruciatingly slow. The process follows this general flow:ġ) Convert the employee's address to Lat-Long values using the GetLatLong functionĢ) Convert the employee's work address to Lat-Long values using the GetLatLong functionģ) Calculate the distance between these two points using the GetDistance functionĤ) Calculate the drive time between these two points using the GetTime function This also includes several other geocoding functions.I am writing VBA in Excel to calculate the distance between an employee's home address and work address using Bing Maps API calls. Option 2: Add this file to your Add-ins directory. =GeocodeAddress(“San Antonio, TX”, BingMapsKey) Now you can use cell references or addresses to geocode your addresses or find the distance between two locations.Į.g. GetStateCountryFromPoint = ("//Address/AdminDistrict").Item(0).Text & ", " & ("//Address/CountryRegion").Item(0).Text If ("//Address/AdminDistrict").Length > 0 Then
![google maps api vba excel google maps api vba excel](https://i.ytimg.com/vi/_P2lj4yHNu4/maxresdefault.jpg)
OHttpReq.Open "get", " " & Lat & "," & Lon & "?o=xml&key=" & BingMapsKey, "false"
![google maps api vba excel google maps api vba excel](https://i.stack.imgur.com/5x1Nv.png)
OHttpReq.Open "get", " " & From & "&wp.1=" & Dest & "&avoid=minimizeTolls&key=" & BingMapsKey, "false"ĭriveDistance = ("//TravelDistance").Item(0).Textįunction GetStateCountryFromPoint(Lat As String, Lon As String, BingMapsKey As String) As String GeocodeAddress = ("//Point/Latitude").Item(0).Text & "," & ("//Point/Longitude").Item(0).Textįunction DriveDistance(From As String, Dest As String, BingMapsKey As String) As String OHttpReq.Open "get", " " & address & "&o=xml&key=" & BingMapsKey, "false" Paste in this code and modify as you see fit:įunction GeocodeAddress(address As String, BingMapsKey As String) As String.Expose the Developer tools (See: Show the Developer Tab).You will need a Bing Maps key (See: Getting a Bing Maps Key), Excel, and an internet connection. This code is provided without warranty, etc. This is similar to asynchronous JavaScript and XML (AJAX) but in this case it would be SVAX. This uses the Bing Maps REST services and synchronous VBA and XML. Wouldn’t it be handy if there was an Excel function like =GEOCODE()? Of course, you can batch geocode using Bing Maps Services, but in some scenarios it is a lot more convenient or reasonable to use Excel. A lot of folks have to geocode data that they get from various folks in their organization, and it often makes its way in to Excel.