API Key Distance Matrxi

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Solved3.52K views

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
GetDistance = -1
End Function

Thank you for your help.


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.


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
You are viewing 1 out of 6 answers, click here to view all answers.

Simply the best place to learn Excel VBA