Difficulty analysing Journey Planner result

[Walter Briscoe]

Journey Planner is a Transport for London facility which calculates a
journey for a customer. I use it a lot, both manually and from VBA.
Manually, I load <http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUES
T2?language=en&ptOptionsActive=1> and complete a form. The code below
calls it from VBA.
I have not found out how to cause buttons to be clicked from VBA.

Should he be interested in the question, Joel could doubtless answer it.

This is code demonstrating where I have got to. As I have not bothered
specifying date and time for the journey, the reader's innerhtml will
vary slightly from mine.
This is my code:

Sub JPcall()
'
' JPcall Macro
' Display the HTML for the first row of a journeyplanner table
'
Dim IE As Object ' IWebBrowser2 ' SHDocVw.InternetExplorer
Dim U As String
Dim Doc As Object ' Object HTMLDocument
Dim Tables As Object ' DispHTMLElementCollection
Dim Tbl As Object

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
U = "http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?" & _
"language=en&sessionID=0&ptOptionsActive=-1" & _
"&type_destination=stop&name_destination=ANGEL" & _
"&type_origin=stop&name_origin=BANK"
IE.Navigate2 U
Do While IE.busy Or IE.ReadyState <> 4
DoEvents
Loop
Set Doc = IE.document
Set Tables = Doc.getElementsByTagname("Table")
Set Tbl = Tables.Item(0)
MsgBox "htmltext = """ & Tbl.innerhtml & """"
'
End Sub


That grabs a particular row and is the sort of thing which contains the
specification of a button I might want to "click".
?Tbl.innerhtml
<TBODY>
<TR>
<TH>Route</TH>
<TH>Depart</TH>
<TH>Arrive</TH>
<TH>Duration</TH>
<TH>Interchanges</TH>
<TH class=viewheader>View</TH></TR>
<TR>
<TD class=option>1</TD>
<TD class=depart>16:00</TD>
<TD class=arrive>16:08</TD>
<TD class=duration>00:08</TD>
<TD class=interchanges><IMG hspace=1 alt=Tube src="assets/images/icon-tube.gif"></TD>
<TD><A class=jpbutton title="View the details for this route" href="XSLT_TRIP_REQUEST2?language=en&amp;sessionID=JP10_2227696266&amp;requestID=1
&amp;tripSelector1=1&amp;itdLPxx_view=detail&amp;tripSelection=on&amp;command=nop&amp;calculateDistance=1">View</A> <INPUT CHECKED type=checkbox
name=tripSelector1 lf="forms[1].tripSelector1"></TD></TR></TBODY>

I am sorry about the long lines - this is the same thing with wrapping:
?Tbl.innerhtml
<TBODY>
<TR>
<TH>Route</TH>
<TH>Depart</TH>
<TH>Arrive</TH>
<TH>Duration</TH>
<TH>Interchanges</TH>
<TH class=viewheader>View</TH></TR>
<TR>
<TD class=option>1</TD>
<TD class=depart>16:00</TD>
<TD class=arrive>16:08</TD>
<TD class=duration>00:08</TD>
<TD class=interchanges><IMG hspace=1 alt=Tube src="assets/images/icon-
tube.gif"></TD>
<TD><A class=jpbutton title="View the details for this route" href="XSLT
_TRIP_REQUEST2?language=en&amp;sessionID=JP10_2227696266&amp;requestID=1
&amp;tripSelector1=1&amp;itdLPxx_view=detail&amp;tripSelection=on&amp;co
mmand=nop&amp;calculateDistance=1">View</A> <INPUT CHECKED type=checkbox
name=tripSelector1 lf="forms[1].tripSelector1"></TD></TR></TBODY>

Thanks!

 

[Walter Briscoe]

In message <[email protected]> of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe

[snip]

I have not found out how to cause buttons to be clicked from VBA.

I have not found out how to click a button but HAVE found out how to run
the button code.

Dim ButtonText As String
Dim RE As Object
Set RE = CreateObject("VBScript.RegExp")

' Grab the code the button runs
RE.Pattern = "(.|\n)*href=.(.*)..View.*$"
ButtonText = RE.Replace(Tbl.innerhtml, "$2")

' s/&amp;/&/g
RE.Pattern = "&amp;"
RE.Global = True
ButtonText = RE.Replace(ButtonText, "&")

' Prefix the site
IE.Navigate2 "http://journeyplanner.tfl.gov.uk/user/" & ButtonText

I will continue by analysing the result of that navigation.

 

[Walter Briscoe]

In message <[email protected]> of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe

Journey Planner is a Transport for London facility which calculates a
journey for a customer. I use it a lot, both manually and from VBA.
Manually, I load <http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUES
T2?language=en&ptOptionsActive=1> and complete a form. The code below
calls it from VBA.
I have not found out how to cause buttons to be clicked from VBA.

Should he be interested in the question, Joel could doubtless answer it.

This is code demonstrating where I have got to. As I have not bothered
specifying date and time for the journey, the reader's innerhtml will
vary slightly from mine.
This is my code:

[snipped original code]

I had a look at the DOM tutorials on <http://w3schools.com> and found
the children attribute. I applied it with the innerhtml attribute as a
probe and managed to get details of one journey. I am confident that a
full understanding of how to drive the Journey Planner is only a matter
of slogging.

This code does the job. There is one long line of about 130 bytes which
may wrap. I would normally use _ to split a long line.
I could not get that to work. Any suggestions?

Option Explicit

Sub JPcall()
'
' JPcall Macro
' Click the "View " button of the first row of a journeyplanner table
'
Dim IE As Object ' IWebBrowser2 ' SHDocVw.InternetExplorer
Dim U As String ' URL apecifying the origin and destination of the Journey
Dim Journeys As Object ' DispHTMLElementCollection
Dim Journey As Object ' HTMLTable
Dim Row0 As Object ' HTMLTableSection
Dim Row1 As Object ' HTMLTableRow
Dim Row2 As Object ' HTMLTableCell
Dim Row3 As Object ' HTMLAnchorElement

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
U = "http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?" & _
"language=en&sessionID=0&ptOptionsActive=-1" & _
"&type_origin=stop&name_origin=BANK" & _
"&type_destination=stop&name_destination=ANGEL"
IE.Navigate2 U
Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop
If 1 = 0 Then
IE.document.getElementsByTagname("Table").Item(0).Children.Item(0).Children.Item(1).Children.Item(5).Children.Item(0).Click
Else
Set Journeys = IE.document.getElementsByTagname("Table")
Set Journey = Journeys.Item(0)
Set Row0 = Journey.Children.Item(0)
Set Row1 = Row0.Children.Item(1)
Set Row2 = Row1.Children.Item(5)
Set Row3 = Row2.Children.Item(0)
Row3.Click ' Get 1st journey details
End If
' Wait until details arrive
Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop
' Detail analysis could be done here
End Sub

 

[ron]

In message <[email protected]> of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe

Journey Planner is a Transport for London facility which calculates a
journey for a customer. I use it a lot, both manually and from VBA.
Manually, I load <http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUES
T2?language=en&ptOptionsActive=1> and complete a form. The code below
calls it from VBA.
I have not found out how to cause buttons to be clicked from VBA.

Should he be interested in the question, Joel could doubtless answer it.

This is code demonstrating where I have got to. As I have not bothered
specifying date and time for the journey, the reader's innerhtml will
vary slightly from mine.
This is my code:

[snipped original code]

I had a look at the DOM tutorials on <http://w3schools.com> and found
the children attribute. I applied it with the innerhtml attribute as a
probe and managed to get details of one journey. I am confident that a
full understanding of how to drive the Journey Planner is only a matter
of slogging.

This code does the job. There is one long line of about 130 bytes which
may wrap. I would normally use _ to split a long line.
I could not get that to work. Any suggestions?

Option Explicit

Sub JPcall()
'
' JPcall Macro
' Click the "View " button of the first row of a journeyplanner table
'
    Dim IE As Object        ' IWebBrowser2 ' SHDocVw.InternetExplorer
    Dim U As String         ' URL apecifying the origin and destination of the Journey
    Dim Journeys As Object  ' DispHTMLElementCollection
    Dim Journey As Object   ' HTMLTable
    Dim Row0 As Object      ' HTMLTableSection
    Dim Row1 As Object      ' HTMLTableRow
    Dim Row2 As Object      ' HTMLTableCell
    Dim Row3 As Object      ' HTMLAnchorElement

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    U = "http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?"& _
        "language=en&sessionID=0&ptOptionsActive=-1" & _
        "&type_origin=stop&name_origin=BANK" & _
        "&type_destination=stop&name_destination=ANGEL"
    IE.Navigate2 U
    Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop
    If 1 = 0 Then
        IE.document.getElementsByTagname("Table").Item(0).Children.Item(0).Children­.Item(1).Children.Item(5).Children.Item(0).Click
    Else
        Set Journeys = IE.document.getElementsByTagname("Table")
        Set Journey = Journeys.Item(0)
        Set Row0 = Journey.Children.Item(0)
        Set Row1 = Row0.Children.Item(1)
        Set Row2 = Row1.Children.Item(5)
        Set Row3 = Row2.Children.Item(0)
        Row3.Click ' Get 1st journey details
    End If
    ' Wait until details arrive
    Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop
    ' Detail analysis could be done here
End Sub

Thanks!

Hi Walter...There is always more than one way to skin a cat using
VBA. In the case at hand, if your goal is simply to select one of the
4 routes and then navigate to the webpage showing the details for that
route, then you might try the following
Sub JPcall()
Dim IE As Object ' IWebBrowser2 ' SHDocVw.InternetExplorer
Dim U As String ' URL apecifying the origin and
destination of the Journey
Dim Journeys As Object ' DispHTMLElementCollection
Dim Journey As Object ' HTMLTable
Dim Row0 As Object ' HTMLTableSection
Dim Row1 As Object ' HTMLTableRow
Dim Row2 As Object ' HTMLTableCell
Dim Row3 As Object ' HTMLAnchorElement


Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
U = "http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?" &
_
"language=en&sessionID=0&ptOptionsActive=-1" & _
"&type_origin=stop&name_origin=BANK" & _
"&type_destination=stop&name_destination=ANGEL"
IE.Navigate2 U
Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop

' now you need to extract the session ID which can be found in the
source code
src_code = IE.document.body.innerhtml
pos_1 = InStr(1, src_code, "sessionID", vbTextCompare)
pos_2 = InStr(pos_1, src_code, "&amp", vbTextCompare)
sessionID = Trim(Mid(src_code, 10 + pos_1, pos_2 - (10 + pos_1)))

nexturl = "http://journeyplanner.tfl.gov.uk/user/" & _
"XSLT_TRIP_REQUEST2?&language=en" & _
"&sessionID=" & sessionID & _
"&requestID=1&tripSelector4=1&itdLPxx_view" & _

"=detail&tripSelection=on&command=nop&calculateDistance=1"

IE.navigate nexturl
end sub

I've arbitrarily set it up to go to trip #4, but you can
programatically change that to whatever you want with an input
box...Ron

 

[Walter Briscoe]

In message <[email protected]
s.com> of Sun, 29 Nov 2009 09:01:55 in microsoft.public.excel.programmin

In message <[email protected]> of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe

[snip]

Hi Walter...There is always more than one way to skin a cat using
VBA. In the case at hand, if your goal is simply to select one of the
4 routes and then navigate to the webpage showing the details for that
route, then you might try the following

Thanks for the response and your efforts, Ron.
My aim is to drive the Journey Planner from VBA without manual
intervention. The code you produced does that. Using inbox to select a
route would not do so.

[snip]

' now you need to extract the session ID which can be found in the
source code
src_code = IE.document.body.innerhtml
pos_1 = InStr(1, src_code, "sessionID", vbTextCompare)
pos_2 = InStr(pos_1, src_code, "&amp", vbTextCompare)
sessionID = Trim(Mid(src_code, 10 + pos_1, pos_2 - (10 + pos_1)))

I tried using a regular expression to duplicate sessionID extraction.
I failed with dishonour.

I've arbitrarily set it up to go to trip #4, but you can
programatically change that to whatever you want with an input
box...Ron

I have successfully written code which calls the Journey Planner form,
completes every field in it, and submits it. There are curiosities in it
where I can get X.clicked to work but X.checked = TRUE gets a 438.
I have commented a few operations to allow the submit to have an output.

Here is that code. I wrote it by looking at innerhtml for the
descendents of doc. I don't like the long line. I could split it into
two statements. I don't know how to split it into two lines.
i.e I do not like
Set Plan = Doc.childNodes.Item(1).Children.Item(1).Children.Item(0).Children.Item(3).Children.Item(2)
I also don't like
Set Plan = Doc.childNodes.Item(1).Children.Item(1).Children.Item(0)
Set Plan = Plan.Children.Item(3).Children.Item(2)
Is there anyway to split into 2 lines, but not 2 statements?
Set Plan = Doc.childNodes.Item(1).Children.Item(1).Children.Item(0)_
.Children.Item(3).Children.Item(2)
gets an error. ;(

Option Explicit

Sub JPcall1()
'
' JPcall1 Macro
' Macro recorded 29/11/2009 by IBM
'
Dim Doc As Object ' HTMLDocument
Dim IE As Object ' IWebBrowser2 ' SHDocVw.InternetExplorer
Dim U As String ' URL apecifying the origin and destination of the Journey
Dim Plan As Object ' Specify the journey in detail
Dim Orig As Object ' Where the journey starts
Dim Dest As Object ' Where the journey ends
Dim When As Object ' Timing of the journey
Dim Xtra As Object ' Extras such as tradeoffs, via point, modes, etc.

Set IE = CreateObject("InternetExplorer.Application")
U = "http://journeyplanner.tfl.gov.uk/user/XSLT_TRIP_REQUEST2?" & _
"language=en&sessionID=0&ptOptionsActive=-1"
' "&type_origin=stop&name_origin=BANK" & _
"&type_destination=stop&name_destination=ANGEL"
IE.Navigate2 U
IE.Visible = True
Do While IE.busy Or IE.ReadyState <> 4: DoEvents: Loop
Set Doc = IE.document
' Doc.Children ' unsupported
Set Plan = Doc.childNodes.Item(1).Children.Item(1).Children.Item(0).Children.Item(3).Children.Item(2)
Set Orig = Plan.Children.Item(21)
Set Dest = Plan.Children.Item(22)
Set When = Plan.Children.Item(23)
Set Xtra = Plan.Children.Item(24)
Orig.Children.Item(9).Value = "lOnDoN"
Orig.Children.Item(11).Click ' Check "Post Code"
Orig.Children.Item(14).Click ' Check "Address"
Orig.Children.Item(17).Click ' Check "Place of interest"
Orig.Children.Item(2).Value = "Angel"
Orig.Children.Item(6).Click ' Check "Station or stop in:"
Dest.Children.Item(2).Value = "Bank" ' Dest & Orig have same layout
When.Children.Item(2).Value = "arr" ' Times are arrivals or departures
When.Children.Item(4).Value = 1 ' on day(1-31)
When.Children.Item(6).Value = 200911 ' Year and Month(200911-201011)
When.Children.Item(9).Value = 6 ' Hour(0-23)
When.Children.Item(11).Value = 55 ' Minute(0-59)
' When.Children.Item(12).Children.Item(0).Click ' Search button click
Xtra.Children.Item(6).Click ' Routes with the fewest changes
Xtra.Children.Item(9).Click ' Routes with the least walking between stops
Xtra.Children.Item(3).Click ' The fastest route
' Xtra.Children.Item(14).Value = "bank" ' I wish to travel via:
Xtra.Children.Item(18).Click ' Station or stop in:
' Xtra.Children.Item(22).Value = "LoNdOn" ' London is default
Xtra.Children.Item(24).Click ' Post code
Xtra.Children.Item(26).Click ' Address
Xtra.Children.Item(28).Click ' Place of interest
' Xtra.Children.Item(35-41,43) have checkboxes one deeper
' x.click is equivalent to x.checked = not x.checked
Xtra.Children.Item(35).Children.Item(0).Click ' cf Doc.getElementById("mode-rail").Click
Xtra.Children.Item(36).Children.Item(0).Click ' mode-dlr
Xtra.Children.Item(37).Children.Item(0).Click ' mode-tube
Xtra.Children.Item(38).Children.Item(0).Click ' mode-tram
Xtra.Children.Item(39).Children.Item(0).Click ' mode-bus
Xtra.Children.Item(40).Children.Item(0).Click ' mode-coach
Xtra.Children.Item(41).Children.Item(0).Click ' mode-river
Doc.getElementById("mode-cycle").Checked = False
Doc.getElementById("mode-tube").Checked = True
' Xtra.Children.Item(44).Children.Item(0).Click ' Search button click
' My mobility requirements
Doc.getElementById("stairs").Click ' I cannot use stairs
Xtra.Children.Item(47).Click ' I cannot use stairs
Doc.getElementById("stairs").Checked = False ' I cannot use stairs
' Xtra.Children.Item(47).Checked = False ' gets 438: unsupported property or method
Xtra.Children.Item(50).Click ' I cannot use escalators
Doc.getElementById("escalators").Checked = False ' I cannot use escalators
Xtra.Children.Item(53).Click ' I cannot use lifts
Doc.getElementById("lifts").Checked = False ' I cannot use lifts
Xtra.Children.Item(56).Click ' I use wheelchair accessible vehicles
Doc.getElementById("wheelchair").Checked = False ' I use wheelchair accessible vehicles
Xtra.Children.Item(62).Click ' I want a cycle only route
Xtra.Children.Item(65).Click ' I want to leave my bicycle at the station
Xtra.Children.Item(68).Click ' I want to take my bicycle on public transport
Doc.getElementById("cycle-minutes").Value = 20 ' I don't want to cycle for longer than X minutes
Xtra.Children.Item(70).Children.Item(0).Value = 2 ' I don't want to cycle for longer than X minutes
Doc.getElementById("walking-minutes").Value = 5 ' I don't want to walk for longer than X minutes
Xtra.Children.Item(72).Children.Item(0).Value = 2 ' I don't want to walk for longer than X minutes
Xtra.Children.Item(75).Value = "slow" ' My walking speed is slow/normal/fast - case-sensitive
Xtra.Children.Item(78).Click ' I'd rather walk if it makes my journey quicker
Xtra.Children.Item(79).Children.Item(0).Click ' Search
'
End Sub

 

[ron]

Walter...I'm not quite following. How will your method select which
of the 4 routes to view without some user input?..Ron

 

[Walter Briscoe]

In message <[email protected]
..com> of Tue, 1 Dec 2009 08:00:40 in microsoft.public.excel.programming,

Walter...I'm not quite following. How will your method select which
of the 4 routes to view without some user input?..Ron

It is probably my fault that you do not follow. My real requirement is
to specify a journey on a particular date and time. I might want the
first journey departing after a time or the last journey arriving before
a time. In some unusual cases, I want a journey departing or arriving at
a precise time. In all cases, only one of the offered journeys matches
the criteria and I will detail that one. I agree that an input box might
be appropriate for others.