We help IT Professionals succeed at work.

Call the Google Maps DistanceMatrix API from VBA

Dale Fye
Dale Fye asked
on
350 Views
Last Modified: 2018-09-24
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

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions