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:
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: