Distances betwteen two zip codes

[cadamhill]

I am looking for a way to reference a zip code of a customer on my estimate sheet against the zip code I life in to determine the approximate distance of the job. Thanks for any help in advance.

[stnkynts]

You would probably need to have a helper sheet with each zip code listed and the approximate distance from where you live. You could then use a lookup function to display it on your estimate sheet.

[cadamhill]

Do you think there is a way to do this using a reference through google maps/earth or mapquest?

[Chippy]

This function uses Google Maps (not the API) and returns the distance between 2 addresses. It can called from VBA or as a worksheet cell function. For example with the start address in A1 and the end address in B1, in C1 enter =GMaps_Distance(A1,B1). The addresses can be any address, location, latitude and longitude coordinates, zip code, post code, etc.

Please Login or Register  to view this content.

[cadamhill]

The macro seems to get hinged up at

Set HTMLdoc = New HTMLDocument

Any suggestions?

[Chippy]

Oops! Change that line to:

Please Login or Register  to view this content.

[cadamhill]

There is still an issue. not sure where. Do you mind taking another look? Thanks. Merry Christmas everyone.

[Chippy]

What is the issue? Does it display an error? Is the distance returned blank or incorrect? Here is the complete code again, including a test routine which displays "2,764 mi" for me.

Please Login or Register  to view this content.

If the distance returned is blank, it means that id=altroute_0 doesn't exist in the Google Maps web page when the code is run in USA, so you will have to examine the HTML source to find the correct id and maybe modify that part of the code slightly.