Calculate distance with Getgoogledistance (decimal separator)

[rickytb]

Hi there,

After some searching I found some code that helps me to calculate the distance between 2 addresses in Excel. However, the outcome is the distance with a dot as decimal separator while I need a comma.

Could someone please support getting this with a comma as decimal separator?

Please find below the formula and code that I am using.
Thanks a lot in advance for your support.

Regards,
Ricky

---------------------------

The formula I am using is:
=IF(OR(A2="";B2="");"";getGoogleDistance(A2;B2))

The code I am using is:

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 = "http://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]

Try replacing:

getGoogleDistance = strDistance

with:

getGoogleDistance = Replace(strDistance, ".", ",")

 

[rickytb]

This works perfectly, thanks a lot for your support and quick answer!

 

[NonStopLeo]

@
John_w



I am trying to calculate distance between 2 addresses given in cell A2 & B2. I have added above code given in post#1 in this thread but It is not working, giving #Name in return. Also it highlights "strHTML" in code.

Can you please help me to fix it? Thanks in advance.

 

[Dave Patton]

Did you provide the full code in post #1 ?

Please post a link to the original code.

 

[NonStopLeo]

@Dave Patton

I am talking about the code given in Post # 1 in this thread posted by "rickytb".

I want to calculate distance between two locations in excel.

<code><code>
</code></code>

 

[John_w]

I am trying to calculate distance between 2 addresses given in cell A2 & B2. I have added above code given in post#1 in this thread but It is not working, giving #Name in return. Also it highlights "strHTML" in code.

The first line of code has been mangled and should be split into two lines:

Const strUnits = "metric" ' imperial/metric (miles/km)
Function CleanHTML(ByVal strHTML)

and then the code should compile and run.

However I think it won't return a valid result because you must specify a "key=YOUR_API_KEY" parameter in the URL. Use the Google Cloud Console to get an API key - https://console.cloud.google.com. Also, use https instead of http.

 

[NonStopLeo]

@John_w
Thanks for your kind response and solution.