Goodday everybody,
For my thesis I'm developing a model in excel which calculates the distance and traveltime of trucks from the depot to the customer. With this model we can create a good basic understanding of the transport.
I still struggle with getting information back from an XML file and to put this in excel. I have made the following script in VBA.
Public Function G_Distance(startGPS As String, eindGPS As String) As Variant
'vervoer As String'
Dim vehicletype As String
Dim Apikey As String
Dim linkAPIkey As String
Dim linkstartGPS As String
Dim linkendGPS As String
Apikey = "&api_key=ee0b8233adff52ce9fd6afc2a2859a28"
linkstartGPS = "&start=" & startGPS
linkendGPS = "&end=" & eindGPS
vehicletype = "&"
''' Link put location to KM'''
Language = "&lang=en"
Maxresponseamount = "&MaxResponse=1"
Distanceunits = "&distunit=KM"
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://openls.geog.uni-heidelberg.de/route?
&routepref=HeavyVehicle" & linkstartGPS & linkendGPS & Language &
Maxresponseamount & Distanceunits & Apikey
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim doc As HTMLDocument
Set doc = IE.document
Dim sDD As String
sDD = Trim(doc.getElementsByTagName("xls:TotalDistance value").innerText)
G_Distance = sDD
End Function
So what this script does is that you put the GPS location of the departure and the GPS location of the destination into 2 different cells (A2 and B2). To start the function you use: =G_distance(A2;B2)
When you start this function it will open up an explorer and here you will see the XML file with the distance in it. Now the only part that doesn't work is to get the data out of the XML into excel.
I hope you guys can help me with this.
Greetings, Jelle
