VBA Module - Google Map error

Hi,

I inherited a module in the macro that has been giving me some problems:

1. I get a compile error due to the users having different versions of Excel (IE. 32bit vs 64bit, 2010 vs 2016)
2. The module script below doesn't always work, (IE. Some area codes won't come up and returns #Name error while others come back with the wrong distance)

I was wondering if there's a better way to get the miles from one zip code in the US to another?

Function GetDirections(Origin As String, Destination As String) As String

' Create a WebClient for executing requests
' and set a base url that all requests will be appended to

Dim MapsClient As New WebClient

MapsClient.BaseUrl = "https://maps.googleapis.com/maps/api/"

' Create a WebRequest for getting directions

Dim DirectionsRequest As New WebRequest

DirectionsRequest.Resource = "directions/{format}"

DirectionsRequest.Method = HttpGet

' Set the request format -> Sets {format} segment, content-types, and parses the response

DirectionsRequest.Format = Json

' (Alternatively, replace {format} segment directly)

DirectionsRequest.AddUrlSegment "format", "json"

' Add parameters to the request (as querystring for GET calls and body otherwise)

DirectionsRequest.AddQuerystringParam "origin", Origin

DirectionsRequest.AddQuerystringParam "destination", Destination

' Force parameter as querystring for all requests

DirectionsRequest.AddQuerystringParam "sensor", "false"

' => GET https://maps.../api/directions/json?....&sensor=false

' Execute the request and work with the response

Dim Response As WebResponse

Set Response = MapsClient.Execute(DirectionsRequest)

If Response.StatusCode = WebStatusCode.Ok Then

' Work directly with parsed json data

Dim Route As Object

Set Route = Response.Data("routes")(1)("legs")(1)

GetDirections = Route("distance")("text")

Else

GetDirections = "Error: " & Response.Content

End If

End Function

Thank you!

.
The Forum server won't let me post the code or attachment here because it believes there is HTML code included.

Here is a link to download a small project that might assist you. It provides mileage and time between two zip codes.

Hope this helps.

https://www.amazon.com/clouddrive/sh...mxy9imaS4GyeJG

Thanks for this, it's def better than what I have. I would need this version to work on a Mac as well, I'm coming up with the error message when I try to run this on a Mac.

.
So sorry. No Mac here. Not familiar.

Ok, thanks for your help.