Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

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

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

Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

In some cases you have to use "&20" for spaces (like the space in front of "VA").
Avatar of Dale Fye

ASKER

Scott,

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

Dale
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial