Dale Fye
asked on
Call the Google Maps DistanceMatrix API from VBA
I'm attempting to create a procedure which will return the distance between two points based on the Google Maps DistanceMatrix API. My initial code (VBA) looks like:
Public Function GoogleDistance(FromAddress As String, ToAddress As String) As String
Dim strMyKey as string
Dim sXMLURL As String
Dim objXMLHTTP As MSXML2.ServerXMLHTTP
On Error GoTo ProcError
strMyKey = "aaaa" '<= this is not the actual key
sXMLURL = "https://maps.googleapis.com/maps/api/distancematrix/xml?" _
& "origin=" & Replace(Replace(FromAddress, " ", " "), " ", "+") & "&" _
& "destination=" & Replace(Replace(ToAddress, " ", " "), " ", "+") & "&" _
& "key=" & strKey
' & "sensor=False" & "&"
Debug.Print sXMLURL
Set objXMLHTTP = New MSXML2.ServerXMLHTTP
With objXMLHTTP
.Open "Get", sXMLURL, False
.setRequestHeader "content-Type", "application/x-www-form-URLEncoded"
.send
End With
Debug.Print objXMLHTTP.responseText
ProcExit:
Exit Function
ProcError:
Debug.Print Err.Number, Err.Description
MsgBox Err.Number & vbCrLf & Err.Description
Resume ProcExit
End Function
At this point, I'd be happy if this would return anything other than:<?xml version="1.0" encoding="UTF-8"?>
<DistanceMatrixResponse>
<status>INVALID_REQUEST</status>
</DistanceMatrixResponse>
I'm sure it is something simple in the formatting of the request string, maybe a comma, a space or something else. The data I'm testing it with generates the following string:https://maps.googleapis.com/maps/api/distancematrix/xml?origin=Grafton,+VA&destination=Yorktown,+VA&key=aaaa
In some cases you have to use "&20" for spaces (like the space in front of "VA").
ASKER
Scott,
Tried that and "%20" in lieu of the "+" to replace spaces in the request. Still getting the INVALID_REQUEST message.
Dale
Tried that and "%20" in lieu of the "+" to replace spaces in the request. Still getting the INVALID_REQUEST message.
Dale
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.