M
Matthew Herbert
All,
My question relates to the code listed in "ListCheckBoxes" below. You
should be able to simply copy and paste all the VBA code below and run
"ListCheckBoxes" in order to follow the logic of my code.
I'm looking for a "better" way of obtaining the collection of web browser
checkbox controls for the following URL:
http://www.zacks.com/screening/custom/view.php?sid=87639 (and a small sample
of the HTML is also below), so that I can check the desired check boxes based
on the text description that lines up with the checkbox. (See the website
for a visual representation).
My Desired Goal:
Since each checkbox has a description (e.g. "52 Week High", "Market Cap
(millions)", etc.), I plan on using the description as a way to identify
whether the checkbox should be checked. The user will have a list of
descriptions in the spreadsheet. The program will then loop through the
spreadsheet list, search the description text for a match (the
".parentElement.parentElement.innerText" portion below, which can be combined
with InStr to find a "match"), and check the checkbox if a match exists.
Again, I'm looking for a "better" way to do this. My IE automation
knowledge is very limited, so I don't know if there is a way to get the
object collection via the class (i.e. "tdata1", and I don't know how or if
getting a collection from the class is possible), the name (i.e.
"p_columns[]"), the tag (i.e. "input"), etc. Or, maybe there is simply a
better way than my current logic. I'm open to suggestions and general
knowledge that may help me better understand IE automation. (For example, is
adding a reference to "Microsoft HTML Object Library", opening the Object
Browser, and then searching the HTML* classes a good way to "discover/learn"
IE automation? It's probably a loaded question that doesn't have a "great"
answer.)
Thanks,
Matthew Herbert
VBA:
Private Sub ListCheckBoxes()
Dim objIE As Object
Dim objTarget As Object
Dim Obj As Object
Dim objArr() As Object
Dim lngCnt As Long
Dim strURL As String
strURL = "http://www.zacks.com/screening/custom/view.php?sid=87639"
Set objIE = GetIE(strURL)
If objIE Is Nothing Then
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate strURL
WaitForLoad objIE
End If
Set objTarget = objIE.document.all.tags("input")
'thought the following might work, but I get an error
'Set objTarget = objIE.docmument.getElementsByName("p_columns[]")
For Each Obj In objTarget
If Obj.Type = "checkbox" Then
ReDim Preserve objArr(lngCnt)
Set objArr(lngCnt) = Obj
lngCnt = lngCnt + 1
End If
Next Obj
For lngCnt = LBound(objArr) To UBound(objArr)
Set Obj = objArr(lngCnt)
With Obj
'listed properties are some of what is viewable
' from "View Source" and what is embedded in
' the tag
'not sure how to list the "class" though
Debug.Print "lngCnt :" & lngCnt
Debug.Print " Prnt.Prnt:" & .parentElement.parentElement.innerText
Debug.Print " name :" & .Name
Debug.Print " type :" & .Type
Debug.Print " value :" & .Value
Debug.Print " id :" & .ID
Debug.Print " innerHTML:" & .innerHTML
Debug.Print " innerText:" & .innerText
'check the box
'.Checked = True
End With
Next lngCnt
End Sub
Function GetIE(strAddress As String) As Object
'---------------------------------------------------------------------
'INFO: Original from Tim Williams
' ??/??/2007, Matthew Herbert
'---------------------------------------------------------------------
Dim objShell As Object
Dim objShellWindows As Object
Dim Obj As Object
Dim objRes As Object
Dim strURL As String
Set objRes = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows
'see if IE is already open
For Each Obj In objShellWindows
strURL = ""
On Error Resume Next
strURL = Obj.document.Location
On Error GoTo 0
If strURL <> "" Then
If strURL Like strAddress & "*" Then
Set objRes = Obj
Exit For
End If
End If
Next Obj
Set GetIE = objRes
End Function
Sub WaitForLoad(objIE As Object)
'---------------------------------------------------------------------
'INFO: Original from Tim Williams
' 07/08/2009, Matthew Herbert
'---------------------------------------------------------------------
Do Until objIE.Busy = False And objIE.readyState = 4
Application.Wait (Now() + TimeValue("0:00:01"))
DoEvents
Loop
End Sub
HTML Sample, which was a simple copy and paste from View Source:
<div class="restable">
<span class="nudge select_choose_header">Select Criteria</span>
<div id="criteriaBox">
<table id="table10000" class="viewCriteria" cellspacing=0 cellpadding=0>
<tr>
<td class="textB" colspan=2>Popular Items</td>
</tr>
<tr><td class=tdata1>Current Zacks Rank
<a class="hand" onclick="show('div_15005');"
onmouseout="hide('div_15005');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_15005" onmouseover="show('div_15005');"
onmouseout="hide('div_15005');">Shortened for brevity's sake</div>
</td>
<td class=tdata1 width=20><a
href="https://www.zacks.com/registration/...ssage=&continue_to=/screening/custom/view.php"><img
src="/images/premium_small.gif" border=0></a></td>
</tr><tr><td class=tdata1>Zacks Industry Rank
<a class="hand" onclick="show('div_15025');"
onmouseout="hide('div_15025');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_15025" onmouseover="show('div_15025');"
onmouseout="hide('div_15025');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><a
href="https://www.zacks.com/registration/...ssage=&continue_to=/screening/custom/view.php"><img
src="/images/premium_small.gif" border=0></a></td>
</tr><tr><td class=tdata1>52 Week High
<a class="hand" onclick="show('div_14010');"
onmouseout="hide('div_14010');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_14010" onmouseover="show('div_14010');"
onmouseout="hide('div_14010');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><input type=checkbox name=p_columns[]
value="14010" onclick="javascript:checkFormForDups(this, this.checked,
14010);"></td>
</tr><tr><td class=tdata1>Market Cap (millions)
<a class="hand" onclick="show('div_12010');"
onmouseout="hide('div_12010');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_12010" onmouseover="show('div_12010');"
onmouseout="hide('div_12010');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><input type=checkbox name=p_columns[]
value="12010" checked onclick="javascript:checkFormForDups(this,
this.checked, 12010);"></td>
</tr><tr><td class=tdata1>Last EPS Surprise (%)
<a class="hand" onclick="show('div_17005');"
onmouseout="hide('div_17005');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_17005" onmouseover="show('div_17005');"
onmouseout="hide('div_17005');"> Shortened for brevity's sake </div>
</td>
My question relates to the code listed in "ListCheckBoxes" below. You
should be able to simply copy and paste all the VBA code below and run
"ListCheckBoxes" in order to follow the logic of my code.
I'm looking for a "better" way of obtaining the collection of web browser
checkbox controls for the following URL:
http://www.zacks.com/screening/custom/view.php?sid=87639 (and a small sample
of the HTML is also below), so that I can check the desired check boxes based
on the text description that lines up with the checkbox. (See the website
for a visual representation).
My Desired Goal:
Since each checkbox has a description (e.g. "52 Week High", "Market Cap
(millions)", etc.), I plan on using the description as a way to identify
whether the checkbox should be checked. The user will have a list of
descriptions in the spreadsheet. The program will then loop through the
spreadsheet list, search the description text for a match (the
".parentElement.parentElement.innerText" portion below, which can be combined
with InStr to find a "match"), and check the checkbox if a match exists.
Again, I'm looking for a "better" way to do this. My IE automation
knowledge is very limited, so I don't know if there is a way to get the
object collection via the class (i.e. "tdata1", and I don't know how or if
getting a collection from the class is possible), the name (i.e.
"p_columns[]"), the tag (i.e. "input"), etc. Or, maybe there is simply a
better way than my current logic. I'm open to suggestions and general
knowledge that may help me better understand IE automation. (For example, is
adding a reference to "Microsoft HTML Object Library", opening the Object
Browser, and then searching the HTML* classes a good way to "discover/learn"
IE automation? It's probably a loaded question that doesn't have a "great"
answer.)
Thanks,
Matthew Herbert
VBA:
Private Sub ListCheckBoxes()
Dim objIE As Object
Dim objTarget As Object
Dim Obj As Object
Dim objArr() As Object
Dim lngCnt As Long
Dim strURL As String
strURL = "http://www.zacks.com/screening/custom/view.php?sid=87639"
Set objIE = GetIE(strURL)
If objIE Is Nothing Then
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate strURL
WaitForLoad objIE
End If
Set objTarget = objIE.document.all.tags("input")
'thought the following might work, but I get an error
'Set objTarget = objIE.docmument.getElementsByName("p_columns[]")
For Each Obj In objTarget
If Obj.Type = "checkbox" Then
ReDim Preserve objArr(lngCnt)
Set objArr(lngCnt) = Obj
lngCnt = lngCnt + 1
End If
Next Obj
For lngCnt = LBound(objArr) To UBound(objArr)
Set Obj = objArr(lngCnt)
With Obj
'listed properties are some of what is viewable
' from "View Source" and what is embedded in
' the tag
'not sure how to list the "class" though
Debug.Print "lngCnt :" & lngCnt
Debug.Print " Prnt.Prnt:" & .parentElement.parentElement.innerText
Debug.Print " name :" & .Name
Debug.Print " type :" & .Type
Debug.Print " value :" & .Value
Debug.Print " id :" & .ID
Debug.Print " innerHTML:" & .innerHTML
Debug.Print " innerText:" & .innerText
'check the box
'.Checked = True
End With
Next lngCnt
End Sub
Function GetIE(strAddress As String) As Object
'---------------------------------------------------------------------
'INFO: Original from Tim Williams
' ??/??/2007, Matthew Herbert
'---------------------------------------------------------------------
Dim objShell As Object
Dim objShellWindows As Object
Dim Obj As Object
Dim objRes As Object
Dim strURL As String
Set objRes = Nothing
Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows
'see if IE is already open
For Each Obj In objShellWindows
strURL = ""
On Error Resume Next
strURL = Obj.document.Location
On Error GoTo 0
If strURL <> "" Then
If strURL Like strAddress & "*" Then
Set objRes = Obj
Exit For
End If
End If
Next Obj
Set GetIE = objRes
End Function
Sub WaitForLoad(objIE As Object)
'---------------------------------------------------------------------
'INFO: Original from Tim Williams
' 07/08/2009, Matthew Herbert
'---------------------------------------------------------------------
Do Until objIE.Busy = False And objIE.readyState = 4
Application.Wait (Now() + TimeValue("0:00:01"))
DoEvents
Loop
End Sub
HTML Sample, which was a simple copy and paste from View Source:
<div class="restable">
<span class="nudge select_choose_header">Select Criteria</span>
<div id="criteriaBox">
<table id="table10000" class="viewCriteria" cellspacing=0 cellpadding=0>
<tr>
<td class="textB" colspan=2>Popular Items</td>
</tr>
<tr><td class=tdata1>Current Zacks Rank
<a class="hand" onclick="show('div_15005');"
onmouseout="hide('div_15005');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_15005" onmouseover="show('div_15005');"
onmouseout="hide('div_15005');">Shortened for brevity's sake</div>
</td>
<td class=tdata1 width=20><a
href="https://www.zacks.com/registration/...ssage=&continue_to=/screening/custom/view.php"><img
src="/images/premium_small.gif" border=0></a></td>
</tr><tr><td class=tdata1>Zacks Industry Rank
<a class="hand" onclick="show('div_15025');"
onmouseout="hide('div_15025');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_15025" onmouseover="show('div_15025');"
onmouseout="hide('div_15025');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><a
href="https://www.zacks.com/registration/...ssage=&continue_to=/screening/custom/view.php"><img
src="/images/premium_small.gif" border=0></a></td>
</tr><tr><td class=tdata1>52 Week High
<a class="hand" onclick="show('div_14010');"
onmouseout="hide('div_14010');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_14010" onmouseover="show('div_14010');"
onmouseout="hide('div_14010');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><input type=checkbox name=p_columns[]
value="14010" onclick="javascript:checkFormForDups(this, this.checked,
14010);"></td>
</tr><tr><td class=tdata1>Market Cap (millions)
<a class="hand" onclick="show('div_12010');"
onmouseout="hide('div_12010');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_12010" onmouseover="show('div_12010');"
onmouseout="hide('div_12010');"> Shortened for brevity's sake </div>
</td>
<td class=tdata1 width=20><input type=checkbox name=p_columns[]
value="12010" checked onclick="javascript:checkFormForDups(this,
this.checked, 12010);"></td>
</tr><tr><td class=tdata1>Last EPS Surprise (%)
<a class="hand" onclick="show('div_17005');"
onmouseout="hide('div_17005');"><img src="/images/help_q.jpg" class="qPlace"
border="0"></a>
<div class=qdiv style="visibility: hidden; border: 1px solid black;"
id="div_17005" onmouseover="show('div_17005');"
onmouseout="hide('div_17005');"> Shortened for brevity's sake </div>
</td>