API Key Distance Matrxi

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...
Solved1.98K views
0

I copied your VBA for the distance matrix and have been using is for a while. It has just stopped working and it appear to now require an API Key. How do I pass this within the VBA? Below is what I have tried. I am only attempting to retrieve a mileage for one address at a time.

‘Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = “https://maps.googleapis.com/maps/api/distancematrix/json?origins=”
secondVal = “&destinations=”
lastVal = “&mode=car&language=pl&sensor=false&Key=place key here”
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
Url = firstVal & Replace(start, ” “, “+”) & secondVal & Replace(dest, ” “, “+”) & lastVal
objHTTP.Open “GET”, Url, False
objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
objHTTP.send (“”)
If InStr(objHTTP.responseText, “””distance”” : {“) = 0 Then GoTo ErrorHandl
Set regex = CreateObject(“VBScript.RegExp”): regex.Pattern = “””value””.*?([0-9]+)”: regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0), “.”, Application.International(xlListSeparator))
GetDistance = CDbl(tmpVal)

Exit Function
ErrorHandl:
GetDistance = -1
End Function

Thank you for your help.

Avatar

I was having the same problem as you and your post actually helped me a bit. Your error I think is just that you have the ‘k’ in key capitalized – use lower case and that worked for me.

0

Hi, google now requires that you generate and use your own Key. Go to the link on the post webpage and create your own Google Distance Matrix API Key then in the code snippet above replce the part:

Key=place key here

selected as best answer
0

Hi,
Unfortunately it not working for me.
I am new in VBA so maybe I am doing something wrong.
I create mi API key on google (I hope that this free of charge API is enough)
I copied you VBA to my module, i can see formule =getDistance, put as it was before (when works) but now I see only result “-1” ( I mean error)

What am I doing wrong?

Is is possible for you to paste fully worked VBA?

thank you in advance.

Changed status to publish
0

Hi,

I have done all the suggestions on this thread and gotten an API key, but I’m still getting the -1 Error. My code is below. Any suggestions?

‘Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = “https://maps.googleapis.com/maps/api/distancematrix/json?origins=”
secondVal = “&destinations=”
lastVal = “&mode=car&language=pl&sensor=false&key=MYKEY”
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = firstVal & Replace(start, ” “, “+”) & secondVal & Replace(dest, ” “, “+”) & lastVal
objHTTP.Open “GET”, URL, False
objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
objHTTP.send (“”)
If InStr(objHTTP.responseText, “””distance”” : {“) = 0 Then GoTo ErrorHandl
Set regex = CreateObject(“VBScript.RegExp”): regex.Pattern = “””value””.*?([0-9]+)”: regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0), “.”, Application.International(xlListSeparator))
GetDistance = CDbl(tmpVal)
Exit Function
ErrorHandl:
GetDistance = -1
End Function

0

I just bought our solution… the first results after enable macros are -1… why?

0

Thanks David & Mike. Just for clarity for any other visitors here, the changes David & Mike have made that make it work are:
1. line 4 is now https
2. line 6 has &key=yourkeyhere added to it

commented on answer
Avatar

Thanks! These adjustments worked for me as well.

Simply the best place to learn Excel VBA