Use VBA to get step polylines from Google Directions API xml file

[jameschristian]

Hi all,
I currently use VBA to extract the 'overview polyline' returned from the Google Directions API, and paste it into a spreadsheet. However, I need more accurate polylines. I can get better accuracy if I can stitch together the child node polylines within each step.

How do I convert my code to go from retrieving the 'overview polyline' to retrieving the a string made up of the polylines from each 'step' from each 'leg'? There are variable (>1) legs and variable (>1) steps. I tried making a loop and just made a complete mess.

Please find attached an XML example. The VBA code is below.

Set objHttp = New MSXML2.XMLHTTP60

With objHttp
    .Open "GET", strURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-URLEncoded"
    .Send
End With
Set objDom = New DOMDocument60
objDom.LoadXML (objHttp.responseText)

Dim strStatus As String
strStatus = objDom.SelectSingleNode("//status").Text
If strStatus = "OK" Then
    ReDim aryDest(0, 1)
        Dim datanode As MSXML2.IXMLDOMNode
        Set datanode = objDom.SelectNodes("//route/overview_polyline")(0)
        
        If datanode.SelectNodes("//status")(0).Text = "OK" Then
            strpoly = datanode.SelectNodes("//route/overview_polyline")(0).Text 'This is where I have tried putting in a loop for each leg and each step, but it is a disaster when I do
            aryDest(0, 0) = strpoly 
        Else
            aryDest(0, 0) = datanode.SelectNodes("status")(0).Text
        End If

Else
    ReDim aryDest(0, 0)
    aryDest(0, 0) = "NO DATA"
End If

 

[John_w]

This shows how to loop through the legs and steps and get the polylines.

'Requires reference to Microsoft XML v6.0

Option Explicit

Public Const APIkey = "YOUR_API_KEY"

Public Sub Test_Directions_API()

    #If VBA7 Then
        Dim XMLhttpReq As XMLHTTP60
        Set XMLhttpReq = New XMLHTTP60
    #Else
        Dim XMLhttpReq As XMLHTTP
        Set XMLhttpReq = New XMLHTTP
    #End If
    Dim status As IXMLDOMNode
    Dim legs As IXMLDOMNodeList, steps As IXMLDOMNodeList
    Dim thisLeg As IXMLDOMNode, thisStep As IXMLDOMNode
    Dim URL As String
    Dim origin As String, destination As String
      
    origin = "Your origin"
    destination = "Your destination"
 
    URL = "https://maps.googleapis.com/maps/api/directions/xml?origin=" & Application.WorksheetFunction.EncodeURL(origin) & "&destination=" & Application.WorksheetFunction.EncodeURL(destination) & "&key=" & APIkey
 
    With XMLhttpReq
        .Open "GET", URL, False
        .send
        Set status = .responseXML.SelectSingleNode("//DirectionsResponse/status")
        Set legs = .responseXML.SelectNodes("//route/leg")
    End With
  
    If status.Text = "OK" Then
 
        Debug.Print legs.Length
        For Each thisLeg In legs
            Set steps = thisLeg.SelectNodes("//step")
            Debug.Print steps.Length
            For Each thisStep In steps
                Debug.Print thisStep.SelectSingleNode("polyline").Text
            Next
        Next
     
    End If
 
End Sub

 

[jameschristian]

Thanks @John_w that works nicely, I see how you did what you did.

Is there a way to get the concatenated polylines from each [step] put into one cell? That is, the result of all "for each thisStep In steps" printed in one cell?

 

[John_w]

You could append them to a string.

Before the loop:

Dim s As String
s = ""

Inside the inner loop:

s = s & thisStep.SelectSingleNode("polyline").Text & ","

After the loop:

Range("A1").Value = Left(s,Len(s)-1)

 

[jameschristian]

As soon as I posted I thought that could be it. Thanks John_w, you're the king!