In message of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe
writes
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!

--
Walter Briscoe

On Nov 29, 12:50*am, Walter Briscoe
wrote:
In message of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe
writes

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!

--
Walter Briscoe

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&calculateDis tance=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

In message
s.com of Sun, 29 Nov 2009 09:01:55 in microsoft.public.excel.programmin
g, ron writes
On Nov 29, 12:50*am, Walter Briscoe
wrote:
In message of Thu, 22 Oct 2009
16:15:23 in microsoft.public.excel.programming, Walter Briscoe
writes

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.

[snip]

Thanks!

--
Walter Briscoe

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.I tem(0).Children.Item(3).Children.Item(2)
I also don't like
Set Plan = Doc.childNodes.Item(1).Children.Item(1).Children.I tem(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.I tem(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.I tem(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

--
Walter Briscoe

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

In message
..com of Tue, 1 Dec 2009 08:00:40 in microsoft.public.excel.programming,
ron writes
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.
--
Walter Briscoe