deciphering html: listbox
 

Hi,

I'm writing vba code in excel to navigate web pages in order to then extract
data from the web pages. I've had good luck so far with basic controls on
web pages. I can enter website login information and have figured out how to
select tabs, but now I have a listbox with filters for pages that I can't
get past. The listbox -- when you click on it normally with the mouse, up
pops a list of filters like "Last Day", "Last 7 Days", etc. as filters for
the specific data to display.

Thus far I've been using code like

Set ipf = ie.Document.all.Item("password")
ipf.Value = "(pword)"
Set ipf = ie.Document.all.Item("LoginForm")
ipf.Click

where ie = CreateObject or ShellWindows.Item to recognize the web page. So
far I've identified Document.all.Items by finding "name=" and "value=" and
"id=" in the html code. With the listbox, I've found the name of the Filter
Item and its options for values, but since it's not a form, I can't find out
how to activate the selected option. Any ideas what html code I should be
looking for?

Can anyone help?

Thanks,

Brian

deciphering html: listbox
 

Brian,

HTML selects have a collection of "option" objects you can look at to
find the one you want - once you know the position then you can set
the list to the correct index.

See if the code below works for you - the function SetList should do
what you want.

Tim.


Sub DoStuff()
Dim o As Object, el As Object

'get a reference to the document object
Set o = GetHTMLDocument("http://www.xe.com/ucc/")

Set el = o.ucc.From 'list has no "id" attribute
'Set el = o.getElementById("From") 'list has an "id"

If Not o Is Nothing Then
If SetList(el, "Canada Dollars - CAD") Then
MsgBox "Set the value"
Else
MsgBox "Value not found"
End If
End If

End Sub



'set the value of a select object to 'SelString'
Function SetList(el, SelString) As Boolean

Dim x As Integer
SetList = False
For x = 0 To el.Options.Length - 1
If el.Options(x).Text = SelString Then
el.selectedIndex = x
SetList = True
Exit For
End If
Next

End Function


'Find an IE window with matching location and get a reference
' to the document object from the loaded page. Assumes no frames.
Function GetHTMLDocument(sAddress As String) As Object

Dim objShell As Object, objShellWindows As Object, o As Object
Dim retVal As Object, sURL As String


Set retVal = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

'see if IE is already open
For Each o In objShellWindows
sURL = ""
On Error Resume Next
sURL = o.Document.Location
On Error GoTo 0
If sURL < "" Then
If sURL Like sAddress & "*" Then
Set retVal = o.Document
Exit For
End If
End If
Next o

Set GetHTMLDocument = retVal
End Function





"Brian Delaney" wrote in message
...
Hi,

I'm writing vba code in excel to navigate web pages in order to then
extract data from the web pages. I've had good luck so far with
basic controls on web pages. I can enter website login information
and have figured out how to select tabs, but now I have a listbox
with filters for pages that I can't get past. The listbox -- when
you click on it normally with the mouse, up pops a list of filters
like "Last Day", "Last 7 Days", etc. as filters for the specific
data to display.

Thus far I've been using code like

Set ipf = ie.Document.all.Item("password")
ipf.Value = "(pword)"
Set ipf = ie.Document.all.Item("LoginForm")
ipf.Click

where ie = CreateObject or ShellWindows.Item to recognize the web
page. So far I've identified Document.all.Items by finding "name="
and "value=" and "id=" in the html code. With the listbox, I've
found the name of the Filter Item and its options for values, but
since it's not a form, I can't find out how to activate the selected
option. Any ideas what html code I should be looking for?

Can anyone help?

Thanks,

Brian

deciphering html: listbox
 

Tim,

Thanks a million. You not only pointed me in the right direction for
nearly (ugh) solving my problem, but, more fortunately for this vba
beginner, your procedure and functions REALLY helped me to get a better
grasp on the HTML object model, the syntaxes for which I'd been
struggling to interpret from MSDN libraries.

I needed to take your procedure one step further than selecting the
option. I also needed to activate it because, unlike the Xe site, the
site I'm working on doesn't have the eqivalent of a 'Submit' button;
rather, just selecting the option causes the navigation to the next
page. I cannabalized your SetList function to accomplish this

Dim o As Object, el As Object, y As String

'get a reference to the document object
Set o =
GetHTMLDocument("http://www.futons.net/index.asp?PageAction=VIEWPROD&Pro
dID=2845")

Set el = o.frmMFGSearch.txtSearch 'list has no "id" attribute
'Set el = o.getElementById("FilterNameSelect") 'list has an "id"
y = "Dream On Futon Covers"

If Not o Is Nothing Then
Dim x As Integer
For x = 0 To el.Options.Length - 1
If el.Options(x).Text = y Then
el.selectedIndex = x
el.fireEvent ("onchange")
Exit For
End If
Next
End If

End Sub

Unfortunately, I still can't get this to work on my target web page
because I can't identify a form to which the listbox object belongs. (I
may be dealing with frames?) The page is organized by multiple Tabs, on
only one of which the listbox appears. I tried substituting the
particular Tab id (as well as a few others) for the form name but no
luck. AND there is also relevant HTML code for a form which does contain
the selected option but

Set el = o.FormName.ListboxName

returns Error 438.

I'm baffled. (And, sorry, I'm reluctant to post the HTML code because
it's for a web page on a brokerage site and I'm new enough to this to
still not know what type of info is safe to post to newsgroups.)

Thanks for your help so far and any more you can offer.

Brian

P.S. The Xe site was a relevant and coincidental reference as I had just
the day before been trying to download info from one of its tables...and
gave up! That said, any tips on just copying a table from a web page in
its HTML format into an excel sheet?

*** Sent via Developersdex http://www.developersdex.com ***

deciphering html: listbox
 

Brian,

If you want to follow up via e-mail then maybe we can sort out your
problem.

saxifrax at pacbell dot net

Tim.


"Brian Delaney" < wrote in message
...
Tim,

Thanks a million. You not only pointed me in the right direction for
nearly (ugh) solving my problem, but, more fortunately for this vba
beginner, your procedure and functions REALLY helped me to get a
better
grasp on the HTML object model, the syntaxes for which I'd been
struggling to interpret from MSDN libraries.

I needed to take your procedure one step further than selecting the
option. I also needed to activate it because, unlike the Xe site,
the
site I'm working on doesn't have the eqivalent of a 'Submit' button;
rather, just selecting the option causes the navigation to the next
page. I cannabalized your SetList function to accomplish this

Dim o As Object, el As Object, y As String

'get a reference to the document object
Set o =
GetHTMLDocument("http://www.futons.net/index.asp?PageAction=VIEWPROD&Pro
dID=2845")

Set el = o.frmMFGSearch.txtSearch 'list has no "id" attribute
'Set el = o.getElementById("FilterNameSelect") 'list has an "id"
y = "Dream On Futon Covers"

If Not o Is Nothing Then
Dim x As Integer
For x = 0 To el.Options.Length - 1
If el.Options(x).Text = y Then
el.selectedIndex = x
el.fireEvent ("onchange")
Exit For
End If
Next
End If

End Sub

Unfortunately, I still can't get this to work on my target web page
because I can't identify a form to which the listbox object belongs.
(I
may be dealing with frames?) The page is organized by multiple Tabs,
on
only one of which the listbox appears. I tried substituting the
particular Tab id (as well as a few others) for the form name but no
luck. AND there is also relevant HTML code for a form which does
contain
the selected option but

Set el = o.FormName.ListboxName

returns Error 438.

I'm baffled. (And, sorry, I'm reluctant to post the HTML code
because
it's for a web page on a brokerage site and I'm new enough to this
to
still not know what type of info is safe to post to newsgroups.)

Thanks for your help so far and any more you can offer.

Brian

P.S. The Xe site was a relevant and coincidental reference as I had
just
the day before been trying to download info from one of its
tables...and
gave up! That said, any tips on just copying a table from a web page
in
its HTML format into an excel sheet?

*** Sent via Developersdex http://www.developersdex.com ***