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,

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

You are viewing 1 out of 6 answers, click here to view all answers.

Simply the best place to learn Excel VBA