Difficulty Finding Table CheckBoxes

Question

I'm trying to use Excel VBA to perform a very manual process in IE. I've worked with VBA and IE enough to do easy tasks like enter text into fields or interact with buttons, but I'm having a difficult time wrapping my head around this.

I have a list of fields in Excel: First Name, Last Name, Email Address. There are over two thousand rows of information in the spreadsheet. The website I'm working with has a list of over three thousand rows of information (same information with the addition of a checkbox), except it only loads a maximum of 100 rows at a time. Every row of data in Excel exists in the web tool.

I'd like to have my procedure iterate over the names and check the corresponding row on the webpage. The problem is figuring out how to identify the input id for the corresponding checkbox. I'm not very familiar with HTML so my code is very rudimentary... I've spent much of the day writing this:

Sub Test()
Dim str As String
Dim ie As SHDocVw.InternetExplorer
Dim doc As HTMLDocument
Dim tbls As Object
Dim tbl As Object
Dim trow As Object
Dim trs As Object
Dim Cell As Range

Set ie = New SHDocVw.InternetExplorer
ie.navigate "https://test.com"
ie.Visible = True
Set doc = ie.Document
Set tbls = doc.getElementsByTagName("TABLE")
For Each Cell In Range("A2:A2200")
    For Each tbl In tbls
        For Each trow In tbl.Rows
            If InStr(1, trow.Cells(i).innerText, Cell.Offset(0, 1) & ", " & Cell) > 0 Then
                Debug.Print trow.Cells(0).innerText
                Stop
            End If
        Next trow
    Next tbl
Next Cell

End Sub

The above code is able to identify the cell on the page that matches the cell in the workbook, but I can't figure out how to find the id to the associated checkbox. The site is private, so I can't provide a link. See below for what appears to be the relevant piece of source code for the site:

        <div>
        <table cellspacing="0" border="0" id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults" style="border-color:Black;border-width:1px;border-style:Solid;width:100%;border-collapse:collapse;">
            <tr>
                <td colspan="6">
                <table id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_tabPager" border="0" cellpadding="5" width="100%">
                    <tr>
                        <td style="border-style:none;">
                            <a id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_lbPrev" disabled="disabled">Previous</a>
                            &nbsp;&nbsp;
                            <a id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_lbNext" disabled="disabled">Next</a>
                        </td>
                        <td style="border-style:none;" align="right">
                            <b>Showing:</b>
                            <span id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow"><input id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_0" type="radio" name="ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow" value="10" checked="checked" /><label for="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_0">10 People</label><input id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_1" type="radio" name="ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow" value="25" onclick="javascript:setTimeout('__doPostBack(\'ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow$1\',\'\')', 0)" /><label for="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_1">25 People</label><input id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_2" type="radio" name="ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow" value="50" onclick="javascript:setTimeout('__doPostBack(\'ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow$2\',\'\')', 0)" /><label for="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_2">50 People</label><input id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_3" type="radio" name="ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow" value="100" onclick="javascript:setTimeout('__doPostBack(\'ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$rblShow$3\',\'\')', 0)" /><label for="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_rblShow_3">100 People</label></span>
                        </td>
                    </tr>
                    <tr id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl01_trSelectOptions">
                        <td colspan="2" style="border-style: none;" align="left">

                                <a href="javascript:SetCheckedAll(true);">Select All on Page</a>
                                &nbsp;&nbsp;
                                <a href="javascript:SetCheckedAll(false);">Clear All on Page</a>
                                &nbsp;&nbsp;

                            <a class="link_w_img" href="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$ctl09&quot;, &quot;&quot;, true, &quot;&quot;, &quot;&quot;, false, true))">
                                    <img src="/images/all.gif" />
                                    <span>Select Entire List</span></a>
                                &nbsp;&nbsp;
                                <a class="link_w_img" href="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl01$ctl11&quot;, &quot;&quot;, true, &quot;&quot;, &quot;&quot;, false, true))">
                                    <img src="/images/none.gif" />
                                    <span>Clear Entire List</span></a>
                        </td>
                    </tr>
                </table>    
            </td>
            </tr>

            <tr class="gridheader" align="left">
                <th scope="col">Select</th>
                <th scope="col">Last Name</th>
                <th scope="col">First Name</th>
                <th scope="col">Middle Name</th>
                <th scope="col">E-mail Address</th>
                <th scope="col" style="width:0px;">&nbsp;</th>
            </tr>

            <tr class="griditem" align="center">
                <td>
                    <input id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_SelectCheckBox" type="checkbox" name="ctl00$ctl00$all_content$all_content$content$ucUserSearch$gvSearchResults$ctl03$SelectCheckBox" />
                </td>
                <td align="left" onmouseover="ShowPopup('ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_divTip');" onmouseout="HidePopup();">Demo1</td>
                <td align="left" onmouseover="ShowPopup('ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_divTip');" onmouseout="HidePopup();">Test</td>
                <td align="left" onmouseover="ShowPopup('ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_divTip');" onmouseout="HidePopup();">&nbsp;</td>
                <td align="left" onmouseover="ShowPopup('ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_divTip');" onmouseout="HidePopup();">[email protected]</td>
                <td style="width:0px;">
                    <div id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_divTip" class="popup">
                        <div class="popupbg">
                            <table>
                                <tr>
                                    <td style="vertical-align:top;">
                                        <span id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_lblName" style="font-weight:bold;">Demo1, Test </span>
                                        <br />E-mail:<span id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_lblEmail">[email protected]</span><br />
                                            <table cellspacing="0" border="0" id="ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_dgDemos" style="border-style:None;border-collapse:collapse;margin-top:-1.25em;">
                                <tr>
                                <td>&nbsp;
                                </td><td>&nbsp;
                                </td>
                        </tr><tr>
                            <td>
                                                            Employee ID: 
                                                        </td><td>
                                                            N/A
                                                        </td>
                        </tr><tr>
                            <td>
                                                            Job Title: 
                                                        </td><td>
                                                            N/A
                                                        </td>
                        </tr><tr>
                            <td>
                                                            Location: 
                                                        </td><td>
                                                            N/A
                                                        </td>
                        </tr>
                    </table>
                                        </td>
                                        <td style="width:50px;vertical-align:top;">
                                            <img src="/images/user_popup.jpg" border="0" width="48" height="48"/>
                                        </td>
                                    </tr>
                                </table>
                            </div>

                </div>
                    </td>

In the above example, the Last Name is Demo1, the First Name is Test, and the Email is [email protected], and the input id for the checkbox is ctl00_ctl00_all_content_all_content_content_ucUserSearch_gvSearchResults_ctl03_SelectCheckBox.

My question is, how do I identify the input id after I've identified table row? Or, perhaps that's impossible and I'm going about this the wrong way? Any thoughts will be much appreciated.

Edit

This is what I tried that gives me the Runtime error (Object variable or With block variable not set):

trow.Cells(0).getElementsByTagName("input")(0).Click

I also tried this:

set input = trow.Cells(0).getElementsByTagName("input")(0)
input.Click

And finally, both these produce the same error:

Debug.Print trow.Cells(0).getElementsByTagName("input")(0).innerText
Debug.Print trow.Cells(0).getElementsByTagName("input")(0)

This is the output of the Debug.Print in my original code:

Demo1, Test
E-mail: [email protected]


Employee ID: N/A
Job Title: N/A
Location: N/A

The only pieces I care about (in the output) are the first two lines, the email and name of the individual.

trow.Cells(0).getElementsByTagName("input")(0) will give you a reference to the checkbox. If all those records are in the same table, then you should probably cache a reference to the correct table once you identify a matching row, so you don't have to scan every table on the page multiple times. — Tim Williams
– Tim Williams, Commented May 5, 2014 at 23:18
I get a Runtime Error 91 when I try that. So maybe my code isn't identifying the proper row? Also, how would I cache a reference to the row? I've never done that before... — ARich
– ARich, Commented May 5, 2014 at 23:28
It would help if you could update your question to show what changes you made when you tried this: post a second block od code with just the relevant lines if you want to leave your original code as-is. Unfortunately trying to debug this kind of problem without actual access to the URL is like trying to paint a room through the keyhole... — Tim Williams
– Tim Williams, Commented May 5, 2014 at 23:53
@TimWilliams Please see my edit. I think the .Click is what's throwing the error. I guess I don't know how to mark the checkbox using your suggestion. I know it's hard to help on questions like these...I am very grateful for your help!! — ARich
– ARich, Commented May 6, 2014 at 4:32
Does the debug.print in your original code give the expected result? i.e. Do you get the HTML for the checkbox? — Tim Williams
– Tim Williams, Commented May 6, 2014 at 5:42

Tim Williams · Accepted Answer · 2014-05-07 06:12:20Z

1

This test worked for me. Looks like the rows you want all have class="griditem", so you can check all table rows for that class name. When you find a match on that and on your cell values, you should be able to find the checkbox as shown.

Sub Tester()

Dim d As New HTMLDocument, trs, rw

    d.body.innerHTML = _
       "<section><table><tr class='griditem'><td>" & _
       "<input type='checkbox' id='id_1'></td>" & _
       "<td>Foo</td></tr><tr class='griditem'><td>" & _
       "<input type='checkbox' id='id_2'></td>" & _
       "<td>Bar</td></tr></table></section>"

    Set trs = d.getElementsByTagName("tr")
    For Each rw In trs
        If rw.className = "griditem" Then
            'check cell values for match....
            Debug.Print rw.Cells(0).getElementsByTagName("input")(0).ID
        End If
    Next rw

End Sub

answered May 7, 2014 at 6:12

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ARich Over a year ago

Tim, I can't thank you enough for working on this! I was able to get it to work after implementing your code. One question, earlier you mentioned caching a reference to a row after the row had been matched. Could you point me in the right direction on how to do that? Are you talking about storing each matched row in an array and checking against the array for each value?

Tim Williams Over a year ago

That was more related to your previous approach and was really more about performance than anything: if you had many values to look up in the table then it might be faster to scan all the rows once, putting the cell values and checkbox id's into some kind of array. Likely the added complexity is not worth it unless the current method's performance is pretty bad.

ARich Over a year ago

Makes sense. I haven't been able to do a full test yet, so I'm not sure what the performance looks like at this point. Although I can say with certainty that it'll be a vast improvement over the manual approach. Thank you again for your help!

Prabhat Srivastava · Accepted Answer · 2014-05-06 00:29:51Z

0

Give a try to this.

Set ElementCol = ie.Document.getElementsByTagName("input")

For Each btnInput In ElementCol

'your code here 


Next btnInput

answered May 6, 2014 at 0:29

Prabhat Srivastava

1971 silver badge7 bronze badges

3 Comments

ARich Over a year ago

This will loop through the input tags, but how would I know if that input tag is the tag that corresponds to the proper row in the workbook? Is there more information in the input tag that I can somehow access and use to do this?

Prabhat Srivastava Over a year ago

you can access input tag by type, name. like btnInput.type = "Submit" then do something.

ARich Over a year ago

Ok, but there's the potential for 100 checkboxes on the page. How do I know if btnInput is the correct checkbox for the current cell in my iteration? Perhaps I'm missing your point?

Collectives™ on Stack Overflow

Difficulty Finding Table CheckBoxes

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related