Call the Google Maps DistanceMatrix API from VBA

Dale Fye
Dale Fye used Ask the Experts™
on
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

Open in new window

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>

Open in new window

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
In some cases you have to use "&20" for spaces (like the space in front of "VA").
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Scott,

Tried that and "%20" in lieu of the "+" to replace spaces in the request.  Still getting the INVALID_REQUEST message.

Dale
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Turns out I needed to use "origins" and "destinations" (plural) rather than "Origin" and "Destination"

Dale

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial