Calculate distance with Getgoogledistance (decimal separator)

rickytb

New Member
Joined
Jul 18, 2018
Messages
15
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:
Code:
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try replacing:
Code:
getGoogleDistance = strDistance
with:
Code:
getGoogleDistance = Replace(strDistance, ".", ",")
 
Upvote 0
@
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.
 
Upvote 0
Did you provide the full code in post #1 ?

Please post a link to the original code.
 
Upvote 0
@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>
 
Last edited:
Upvote 0
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top