API Key Distance Matrxi

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...
Solved3.51K 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,
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
You are viewing 1 out of 6 answers, click here to view all answers.

Simply the best place to learn Excel VBA