using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

foxozaur

New Member
Joined
Sep 24, 2019
Messages
4
Dear members of the forum,

I'm struggling with a mind-numbing task that needs immediate automation. While we're looking for cheap alternatives to employ for automating kilometer calculation from point A to point B, I thought it's best to inquire the forum what are the possible options to 1) create a hyperlink for each lane (Point A to Point B) in order to populate and 2) extract all the values from the hyperlink straight into the spreadsheet without clicking every single hyperlink to extract the distance in kilometers.

Every day, we receive more than 500+ lanes to evaluate and calculate their distance, and it got to the point where it's incredibly mind numbing for the whole team to spend 1/2 of the work day trying to add the kilometers from google maps onto the excel spreadsheet.

Any tips will be greatly appreciated!

Best

Foxozaur
 

foxozaur

New Member
Joined
Sep 24, 2019
Messages
4
The easiest way would be to use Google's distance matrix API. https://developers.google.com/maps/documentation/distance-matrix/intro
The cost is about $5.00 US per 1000 hits. Another way, I suppose, would be to scrape the results of each individual search. Define point A, B. Are these addresses? Geo?
Thank you a lot, Tom!

The reason for inquiry is for undue kilometers based on Geocoded Origin & Destination city, post codes, country codes. Although each corporation has a different definition for Geocoding, I'm referring to a 3-5km radius based on full Origin & Destination post codes.
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Fox, what is an example of a "full Origin and Destination post code" in your country?
 

foxozaur

New Member
Joined
Sep 24, 2019
Messages
4
Tom, in the context of the Netherlands, a hypothetical situation - Origin post code is 2515AL (shortened would be the first two digits, 25) and destination code is 2621GB (shortened - 26).
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Fox, I tried to run a query in a plain old Google search and it could not find the 2621GB. It did find the first one. I'm not sure if I am going to be able to help you out or not. The Google API may be your best bet. There are other companies out there that offer the same service. If that's not an option, you should be able to use Google. I'll need you to do a Google search that returns the result you are looking for and then post the query string in here. Have a good day!
 

foxozaur

New Member
Joined
Sep 24, 2019
Messages
4
Tom, apologies - I listed random post codes over there, expected the destination code to be operational. Anyways, I contacted Google Maps customer service, among many other distance calculation and route optimization companies, and we're onto something.

Thank you for taking the time and providing an amazing lead.
 

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top