Calculating zip to zip distance with google distance api

[ModestMuse]

Hello!

I have tried to follow the code in this post however I am getting a return of "object variable or With block variable not set"

I am assuming it's because I am using zip codes rather than addresses as the OP mentions addresses but please advise if not. I do want to note that I updated the URL in the code to https://maps.googleapis.com/maps/api/directions/xml&key=mykey as later in the thread someone mentions to add your API key so not sure if I did that correctly either.

Thank you for whatever help you can provide. I've decided I need to take coding classes haha

The code for those of you that don't want to have to go to the thread:

Const strUnits = "metric" ' imperial/metric (miles/km)
Function CleanHTML(ByVal strHTML)
Dim strInstrArr1() As String
Dim strInstrArr2() As String
Dim s As Integer


strInstrArr1 = Split(strHTML, "<")
For s = LBound(strInstrArr1) To UBound(strInstrArr1)
   strInstrArr2 = Split(strInstrArr1(s), ">")
   If UBound(strInstrArr2) > 0 Then
        strInstrArr1(s) = strInstrArr2(1)
   Else
        strInstrArr1(s) = strInstrArr2(0)
   End If
Next
 CleanHTML = Join(strInstrArr1)
End Function
Function gglDirectionsResponse(ByVal strStartLocation, ByVal strEndLocation, ByRef strTravelTime, ByRef strDistance, ByRef strInstructions, Optional ByRef strError = "") As Boolean
On Error GoTo errorHandler
Dim strURL As String
Dim objXMLHttp As Object
Dim objDOMDocument As Object
Dim nodeRoute As Object
Dim lngDistance As Long


Set objXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set objDOMDocument = CreateObject("MSXML2.DOMDocument.6.0")
 
strStartLocation = Replace(strStartLocation, " ", "+")
strEndLocation = Replace(strEndLocation, " ", "+")
 
strURL = "https://maps.googleapis.com/maps/api/directions/xml" & _
            "?origin=" & strStartLocation & _
            "&destination=" & strEndLocation & _
            "&sensor=false" & _
            "&units=" & strUnits
With objXMLHttp
    .Open "GET", strURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-URLEncoded"
    .send
    objDOMDocument.LoadXML .responseText
End With
With objDOMDocument
    If .SelectSingleNode("//status").Text = "OK" Then
        lngDistance = .SelectSingleNode("/DirectionsResponse/route/leg/distance/value").Text ' Retrieves distance in meters
        Select Case strUnits
            Case "imperial": strDistance = Round(lngDistance * 0.00062137, 1)
            Case "metric": strDistance = Round(lngDistance / 1000, 1)
        End Select
        strInstructions = CleanHTML(strInstructions)
        Else
        strError = .SelectSingleNode("//status").Text
        GoTo errorHandler
    End If
End With
 gglDirectionsResponse = True
GoTo CleanExit
errorHandler:
    If strError = "" Then strError = Err.Description
    strDistance = -1
    gglDirectionsResponse = False
CleanExit:
    Set objDOMDocument = Nothing
    Set objXMLHttp = Nothing
 End Function
 Function getGoogleDistance(ByVal strFrom, ByVal strTo) As String
Dim strTravelTime As String
Dim strDistance As String
Dim strError As String
Dim strInstructions As String
If gglDirectionsResponse(strFrom, strTo, strTravelTime, strDistance, strInstructions, strError) Then
    getGoogleDistance = strDistance
Else
    getGoogleDistance = strError
End If
End Function

 

[John_w]

I have tried to follow the code in this post however I am getting a return of "object variable or With block variable not set"

Which line causes that error?

I do want to note that I updated the URL in the code to https://maps.googleapis.com/maps/api/directions/xml&key=mykey as later in the thread someone mentions to add your API key so not sure if I did that correctly either.

Why don't you post your code then, with the &key= in the URL, so we can see if you've done it correctly? The URL should be:

strURL = "https://maps.googleapis.com/maps/api/directions/xml" & _
            "?origin=" & strStartLocation & _
            "&destination=" & strEndLocation & _
            "&key=" & APIkey & _
            "&units=" & strUnits

where APIkey is:

Dim APIkey As String
APIkey="your_API_key"