AW: Fahrkilometer ermitteln per Google
13.06.2016 15:38:41
Michael
Hi Jens,
ich lade alles mal in ein Array und durchlaufe dieses:
Option Explicit
Private Sub CommandButton1_Click()
'Variablendeklarastionen
'Objekt - Late Binding
Dim objXML As Object 'fuer XML-"String"
Dim xmlDoc As Object
Dim xmlNod As Object
Dim z&, s&, maxz&, maxs& ' Zeile, Spalte &=as long
Dim a As Variant
'Objekt - Early Binding
'Dim xmlDoc As New MSXML2.DOMDocument
'Dim xmlNod As MSXML2.IXMLDOMNode
'String
Dim strOAddr$, strDAddr
'On Error GoTo errorhandler
'XML-Objecte instanzieren
Set objXML = CreateObject("Msxml2.XMLHTTP")
'Wenn Instanzierung nichts gebracht hat, dann raus
If objXML Is Nothing Then MsgBox "msxml2-Fehler": Exit Sub
Set xmlDoc = CreateObject("MSXML2.DOMDocument")
maxz = Range("A" & Rows.Count).End(xlUp).Row
maxs = Cells(1, Columns.Count).End(xlToLeft).Column
a = Range("A1").Resize(maxz, maxs)
For z = 2 To maxz
If InStr(a(z, 1), ",") 6 Then
a(z, 2) = "PLZ?"
Else
strOAddr = "Deutschland, " & a(z, 1)
For s = 2 To maxs
If InStr(a(1, s), ",") 6 Then
a(z, 2) = "PLZ?"
Else
strDAddr = "Deutschland, " & a(1, s)
objXML.Open "POST", _
"http://maps.googleapis.com/maps/api/" & _
"distancematrix/xml?origins=" & strOAddr & _
"&destinations=" & strDAddr & _
"&language=de-DE&sensor=false", False
objXML.setRequestHeader "Content-Type", _
"content=text/html; charset=UTF-8"
objXML.send
xmlDoc.LoadXML objXML.responseText
Set xmlNod = xmlDoc.SelectSingleNode _
("//row/element/distance/value")
If Not xmlNod Is Nothing Then
a(z, s) = Val(xmlNod.Text) / 1000
Else
a(z, s) = "n.v."
End If
End If
Next
End If
Next
Range("A1").Resize(maxz, maxs) = a
Err.Clear
'Fehlerbehandlung / Programmende
errorhandler:
'Wenn Fehlernummer 0, dann Ausgabe Fehlermeldung
If Err.Number 0 Then MsgBox "Fehler: " & Err.Number & vbLf & Err.Description
'XML-Objecte zuruecksetzen
Set xmlNod = Nothing
Set xmlDoc = Nothing
Set objXML = Nothing
End Sub
Allerdings stelle ich fest, daß das Ding anscheinend Probleme mit Umlauten hat: irgendeine Anschrift in Regensburg geht, in Nürnberg nicht.
Schöne Grüße,
Michael