Capture values from a web page drop-down list

[Paul Martin]

OK, I posted about this previously, seeking code not browser-
dependent. I've revised my plan and am now attempting to capture the
values in a web page drop-down list, via Internet Explorer. I have
identified the element that contains the drop-down and can capture its
current value, but I want to capture all the values in the list. Any
suggestions?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Tim Williams]

Just loop through the options and check the .value or .text properties
(depending on which you need).

Dim o as object
for each o in objList.options
debug.print o.value & ":" & o.text
next o

where objList is your reference to the list.

Tim

 

[Paul Martin]

Thanks Tim

I've got it working, though I've refined it slightly by declaring my
variable as type MSHTML.HTMLOptionElement (which I ascertained by
debugging the object with typename.

Paul

 

[Paul Martin]

I have my code working by firing up Internet Explorer, but it'd be
great to have a solution that simply used the html protocols to
interrogate a webpage drop-down, independent of browsers. Any other
suggestions?

 

[Paul Martin]

This is what I have so far:

Dim HttpReq As New MSXML2.XMLHTTP
Dim HtmlDoc As New MSHTML.HTMLDocument

HttpReq.Open "GET", HTTP_PATH, False
HttpReq.send

Debug.Print HttpReq.Status

If HttpReq.Status = HTTPREQ_SUCCESS Then
HtmlDoc.body.innerHTML = HttpReq.responseText
End If

What I would like to know is if at this point in the code, HtmlDoc is
a variable representation of the webpage or whether it remains empty
until I 'navigate' to the URL.

I seem to be able to capture a form, as the following works:
Debug.Print HtmlDoc.forms("aspnetForm").Name

But the following fails (the item is the name of the drop-down list) &
I get Run-time error '91': Object variable or With block variable not
set:
Debug.Print HtmlDoc.forms("aspnetForm").Item
("ctl00$ContentPlaceHolder1$dpRecentReport").Name

Any suggestions?


I have been unsuccessful in capturingdrop-down list from the web
page, eg, the following fails:

Debug.Print HtmlDoc.forms("aspnetForm").Name

 

[Tim Williams]

You should not have to "navigate" once the document is loaded.

Tim

'******************************
Sub TestMSHTML()
Dim oForm As Object, oEl As Object
Dim oMSHTML As New MSHTML.HTMLDocument
Dim oDoc As New MSHTML.HTMLDocument


Set oDoc = oMSHTML.createDocumentFromUrl( _
"http://google.com", vbNullString)
Do While oDoc.readyState <> "complete"
DoEvents
Loop

For Each oForm In oDoc.forms
Debug.Print "Form: " & oForm.Name
For Each oEl In oForm.elements
Debug.Print " Element: " & oEl.Name
Next oEl
Next

End Sub
'******************************

 

[ron]

You should not have to "navigate" once the document is loaded.

Tim

'******************************
Sub TestMSHTML()
Dim oForm As Object, oEl As Object
Dim oMSHTML As New MSHTML.HTMLDocument
Dim oDoc As New MSHTML.HTMLDocument

    Set oDoc = oMSHTML.createDocumentFromUrl( _
             "http://google.com", vbNullString)
    Do While oDoc.readyState <> "complete"
        DoEvents
    Loop

    For Each oForm In oDoc.forms
        Debug.Print "Form:  " & oForm.Name
        For Each oEl In oForm.elements
            Debug.Print "   Element:  " & oEl.Name
        Next oEl
    Next

End Sub
'******************************
















- Show quoted text -

This method doesn't work because (at least for me) when you proceed to
the target url, you first encounter a "Terms of Use" page. Only after
you click on the "I agree" button do you pass through to the desired
web page. Using IE, the following line of code successfully clicked
the "I Agree" button and allowed me to pass through.

ie.document.all.Item("ctl00$ContentPlaceHolder1$imgAgree").Click

I have tried the "Post" method using the following code, but haven't
found the correct "Send" terms that would allow me to "click" the
button using the "Post" method. I simply remain stuck on the "Terms
of Use" page...Ron

Set my_obj = CreateObject("MSXML2.XMLHTTP")
my_obj.Open "POST", "http://192.120.13.77/viewtable.aspx?
region=E_AUST&report=int924", False
my_obj.setRequestHeader "Content-Type", "application/x-www-form-
urlencoded"
my_obj.Send "ctl00$ContentPlaceHolder1$imgAgree=click"
my_var = my_obj.responseText
Set my_obj = Nothing
Debug.Print my_var

 

[Paul Martin]

Tim, your code finds the form but not the button
"ct100_ContentPlaceHolder1_imgAgree".
This fails: Debug.Print oForm.elements
("ct100_ContentPlaceHolder1_imgAgree").Name

Ron, I already have the IE solution working, but am trying to find a
browser-independent solution. And as you mention, the second
suggestion doesn't get past the "I Agree" button.

Thanks for the suggestions so far. I'm wracking my brain over this
one, so if anyone can get further on it, it'd be much appreciated.

Paul

 

[Paul Martin]

I've been able to progress Tim's code as below. I can capture the "I
Agree" button, but the click doesn't seem to work and I don't progress
from the disclaimer page to that desired. Any suggestions to get the
click to work?

******************************
Sub TestMSHTML()
Dim oForm As MSHTML.HTMLFormElement
Dim oEl As MSHTML.HTMLInputButtonElement
Dim oMSHTML As New MSHTML.HTMLDocument
Dim oDoc As New MSHTML.HTMLDocument
Dim strURL as string

strURL = "http://192.120.13.77/viewtable.aspx?
region=E_AUST&report=int924"

Set oDoc = oMSHTML.createDocumentFromUrl( _
strURL, vbNullString)

Do While oDoc.readyState <> "complete"
DoEvents
Loop

For Each oForm In oDoc.forms
Debug.Print "Form: " & oForm.Name
For Each oEl In oForm.all ' <<<CHANGED elements TO all
Debug.Print " Element: " & oEl.ID
if oEl.ID = "ctl00_ContentPlaceHolder1_imgAgree" Then
oEl.Click ' <<< NO ERROR, BUT CLICK DOESN'T PROGRESS
TO NEXT WEBPAGE!!!
End If
Next oEl
Next

End Sub
'******************************

 

[Tim Williams]

When I run this I get an error from a script in the loaded document as soon
as it has loaded

if (document.getElementById('webHeader'))
{
webHeader.setLogInButton('false');
}

Possibly there's something required by the login which isn't being loaded
(or loaded correctly) in the non-browser environment....

Tim

 

[Paul Martin]

I'm not sure what you mean, Tim, as there is no requirement to login,
just to click the "I Accept" button.

Paul

 

[Tim Williams]

What I mean is that when I run your code in the VBE in XL I get an error
when the page loads within the MSHTML document object.
The error comes from javascript in the page: when I hit "debug" it takes me
to the MS script debugger which hilights the bit of javascript code I
posted.

Tim

 

[Paul Martin]

That's strange, Tim. When I run the code, I don't get an error at
all. But oDoc remains on the original disclaimer page, not the
desired page. Any other ideas?

 

[Tim Williams]

Possibly the error message on my PC is because I have development tools
installed.

In any case, I'm not sure what to suggest to get the button clicked.
When an image is used as a submit button it passes a couple of parameters
like:

image_name.x = [x-coord of click]
image_name.y = [y-coord of click]

Possibly you could fake these by adding a couple of inputs to the form using
code and then submitting the form using the form's submit() method.

To help out I'd recommend installing Fiddler and using it with IE: this will
let you see what gets passed when the page is submitted manually.
http://www.fiddler2.com/fiddler2/

I've found this v. useful in the past. I believe there are similar tools
for FireFox (?)


Tim

 

[Paul Martin]

With VBA, I can navigate past the "I Agree" button, select a file from
the 'Recent Report' dropdown list and click the Save button. A File
Download dialog box then appears; can someone suggest how I can
programmatically click the Save button on this dialog box and save it
to the desktop?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Tim Williams]

If it helps, each of those files in the drop-down is located in this
directory:

http://192.120.13.77/Data/Public/E_AUST/Archive/

So you may just be able to download it directly from there.

Tim

 

[Paul Martin]

Tim, when you say "you may just be able to download it directly from
there", in what way specifically?

If I use IE, I get the 'File Download' dialog box. If I use the
URLDownloadToFile API, using that URL, it merely downloads the
Disclaimer page. I'm stuck on how to proceed. Any suggestions?

Paul

 

[Tim Williams]

You could try using xmlhttp to grab the file using a GET request.
It might be necessary though to add the session cookies that get set in IE
when you go through the Disclaimer page. You can extract these from IE
using "document.cookie" and then add them as headers to your xmlhttp
request.

Downloading with xmlhttp (ASP example):
http://www.4guysfromrolla.com/webtech/110100-1.shtml

Adding cookies:
http://support.microsoft.com/default.aspx/kb/290899

Time to sleep now: I'll have a go at this myself tomorrow.

Tim