Avatar of Dale Fye
Dale Fye
Flag 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

GoogleGoogle WorkspaceVBA

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Scott McDaniel (EE MVE )

In some cases you have to use "&20" for spaces (like the space in front of "VA").
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
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck