Find each instance of an HTML tag with only the partial name

Question

I am attempting to manipulate Internet Explorer, inside of an intranet, with Excel VBA, in order input data from Excel into the appropriate data fields in the web page. In order to make the data fields available check boxes must be ticked. Initially, when there was never any data that had been entered into these, I thought the naming mechanic was always checkBoxSum6 for the first row with intervals of seven between each row. After entering data this is no longer the case.

I now need to find a way to read the entire document for each of the checkBoxSum[x] items, click them, and store the numeric piece of their name to a variable, possibly an array. I would be storing them in order to use them to reference the input boxes named fVolumeEditSum[x] and fCommentsSumEdit[x].

I have looked through threads such as this. I have also searched the Stack for solutions that appear to come close to this, but everything I have found looks to return the data value of the given reference rather than the name of the reference itself.

The bits of code I had that iterated through this problem prior to learning the full scope of the web page is:

Option Explicit
Sub OpenWebPage()

Dim IE As InternetExplorerMedium
Dim x As Integer, n As Integer
Dim firstDate As Date, secondDate As Date

n = DateDiff("ww", firstDate, secondDate)
x = 6

Do While n > 0
    IE.Document.all("checkBoxSum" & x).Click
    n = n - 1
    x = x + 7
Loop

End Sub

Below is the HTML code. It runs through the second insance of checkBoxSum.

<tr>
    <td colspan="2">
    <table cellpadding="0" cellspacing="0" border="0" class="dataTable" id="reportTable">
        <thead>
            <tr bgcolor="#E8E8E8">
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b></b></span></td><!-- First icon -->
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b></b></span></td><!-- Second icon -->
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Update</b></span></td>
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Date</b></span></td>
                <td width="73" align="left" style="display: none"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Business</b></span></td>
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Volume</b></span></td>
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Lock</b></span></td>
                <td width="73" align="left"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Comments&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</b></span></td>
                <td width="73" align="left" style="display: none"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>Current</b></span></td>
                <td width="73" align="left" style="display: none"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>ArrayRow</b></span></td>
                <td width="73" align="left" style="display: none"><span style="text-align:center; font-size:12px; white-space: nowrap"><b>RowType</b></span></td>
            </tr>
        </thead>
        <tbody>














             <!-- Counter starts at 1 array at 0 -->
             <!-- Counter starts at 1 array at 0 -->
             <!-- Counter starts at 1 array at 0 -->








                        <!-- current_N -->
                            <tr style="display: none" class="main"></tr><tr id="mainRow20" class="main" myindex="20">
                            <td>
                                <div id="iconShowA20" class="ui-icon ui-icon-plusthick" onclick="showExpand('20','20')" ;=""></div>
                                <div id="iconShowB20" class="ui-icon ui-icon-minusthick" style="display: none" onclick="hideExpand('20','20')" ;=""></div>
                            </td>
                            <td align="left"></td><!-- Second icon -->
                            <td align="left">
                            <input type="checkbox" name="checkBoxSum20" id="checkBoxSum20" onclick="checkItWeek('20','20')" ;=""></td>
                            <td align="left"><div id="divWeekDTSum20"><b>10/12/2015</b></div></td>
                            <td align="left" style="display: none"></td>
                            <td align="left" style="display: none"><div id="divBusinessSum20">PL_LEGACY</div></td>
                                <td align="left" id="TDfVolumeSum20"><b>0</b></td>
                                <td align="left" id="TDfVolumeSumEdit20" style="display: none"><b><input type="text" class="allowedValues" id="fVolumeEditSum20" name="fVolumeEditSum20" value="0" onkeyup="fVolumeEditKeyUpSum(20)" ;="" size="5"> </b></td>
                            <td align="left">
                                <input type="checkbox" id="lockSum20" name="lockSum20" onclick="fVolumeEditKeyUpSum(20)" ;="">
                            </td>
                                <td id="TDfCommentsSum20" align="left"><b></b></td>
                                <td id="TDfCommentsSumEdit20" style="display: none" align="left"><input type="text" onkeyup="fVolumeEditKeyUpSum(20)" ;="" id="fCommentsSumEdit20" name="fCommentsSumEdit20"></td>
                            <td align="left" style="display: none"><b></b></td>
                            <td align="left" style="display: none"><input type="text" id="arrayStoreSum20" name="arrayStoreSum20" readonly="readonly"></td>
                            <td align="left" style="display: none"><div>MainSum</div></td>
                        </tr><tr class="expandableDay" id="rowShow2" style="display: none" bgcolor="#EBEEF2" title="Creator : LUSCHR 
Create Date : 09/10/2015">
                                    <td align="left"></td><!-- First icon -->
                                    <td>
                                        <div id="iconShowA_N2" class="ui-icon ui-icon-plusthick" onclick="showExpandSub('2','2')" ;=""></div>
                                        <div id="iconShowB_N2" class="ui-icon ui-icon-minusthick" style="display: none" onclick="showExpandSub('2','2')" ;=""></div>
                                    </td>
                                    <td align="left">

                                            <input type="checkbox" name="checkBox2" id="checkBox2" onclick="checkIt(2);">       

                                    </td>
                                    <td bgcolor="#EBEEF2" align="left" style="display: none"><div id="divWeekDT2">10/12/2015</div></td>
                                    <td bgcolor="#EBEEF2" align="left"><div id="divActivityDT2">10/12/2015</div></td>
                                    <td bgcolor="#EBEEF2" align="left" style="display: none"><div id="divBusiness2">PL_LEGACY</div></td>
                                        <td id="TDfVolume2" bgcolor="#EBEEF2" align="left">0</td>
                                        <td id="TDfVolumeEdit2" style="display: none" bgcolor="#EBEEF2" align="left"><input type="text" class="allowedValues" onkeyup="fVolumeEditKeyUp(2)" ;="" name="fVolumeEdit2" id="fVolumeEdit2" size="5" value="0"> </td>
                                    <td bgcolor="#EBEEF2" align="left"><div id="divLockFlag2">N</div></td>
                                        <td id="TDfComments2" bgcolor="#EBEEF2" align="left"> </td>
                                        <td id="TDfCommentsEdit2" style="display: none" bgcolor="#EBEEF2" align="left"><input type="text" onkeyup="fVolumeEditKeyUp(2)" ;="" id="fCommentsEdit2" name="fCommentsEdit2"></td>
                                    <td bgcolor="#EBEEF2" align="left" style="display: none">Y</td>
                                    <td bgcolor="#EBEEF2" align="left" style="display: none"><input type="text" id="arrayStoreRow2" name="arrayStoreRow2" readonly="readonly"></td>
                                    <td bgcolor="#EBEEF2" align="left" style="display: none"><div>current_Y_N</div></td>
                                </tr><tr class="current_N" id="current_N1" ncnt="1" style="display: none" bgcolor="#F6F6F6" title="Creator : LUSCHR 
Create Date : 09/10/2015">
                                <td align="left"></td><!-- First icon -->
                                <td align="left"></td><!-- Second icon -->
                                <td align="left"></td><!-- Checkbox -->
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div id="divWeekDT1">10/12/2015</div></td>
                                <td bgcolor="#F6F6F6" align="left"><div id="divActivityDT1">10/12/2015</div></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div id="divBusiness1">PL_LEGACY</div></td>
                                    <td id="TDfVolume1" bgcolor="#F6F6F6" align="left">.19</td>
                                    <td id="TDfVolumeEdit1" style="display: none" bgcolor="#F6F6F6" align="left"><input type="text" class="allowedValues" onkeyup="fVolumeEditKeyUp(1)" ;="" name="fVolumeEdit1" id="fVolumeEdit1" size="5" value=".19"> </td>
                                <td bgcolor="#F6F6F6" align="left"><div id="divLockFlag1">N</div></td>
                                    <td id="TDfComments1" bgcolor="#F6F6F6" align="left"> </td>
                                    <td id="TDfCommentsEdit1" style="display: none" bgcolor="#EBEEF2" align="left"><input type="text" onkeyup="fVolumeEditKeyUp(1)" ;="" id="fCommentsEdit1" name="fCommentsEdit1"></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none">N</td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><input type="text" id="arrayStoreRow1" name="arrayStoreRow1" readonly="readonly"></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div>current_N</div></td>
                            </tr><tr class="current_N" id="current_N0" ncnt="0" style="display: none" bgcolor="#F6F6F6" title="Creator : ADMIN 
Create Date : 12/06/2012">
                                <td align="left"></td><!-- First icon -->
                                <td align="left"></td><!-- Second icon -->
                                <td align="left"></td><!-- Checkbox -->
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div id="divWeekDT0">10/12/2015</div></td>
                                <td bgcolor="#F6F6F6" align="left"><div id="divActivityDT0">10/12/2015</div></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div id="divBusiness0">PL_LEGACY</div></td>
                                    <td id="TDfVolume0" bgcolor="#F6F6F6" align="left">0</td>
                                    <td id="TDfVolumeEdit0" style="display: none" bgcolor="#F6F6F6" align="left"><input type="text" class="allowedValues" onkeyup="fVolumeEditKeyUp(0)" ;="" name="fVolumeEdit0" id="fVolumeEdit0" size="5" value="0"> </td>
                                <td bgcolor="#F6F6F6" align="left"><div id="divLockFlag0">N</div></td>
                                    <td id="TDfComments0" bgcolor="#F6F6F6" align="left">place holder
</td>
                                    <td id="TDfCommentsEdit0" style="display: none" bgcolor="#EBEEF2" align="left"><input type="text" onkeyup="fVolumeEditKeyUp(0)" ;="" id="fCommentsEdit0" name="fCommentsEdit0"></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none">N</td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><input type="text" id="arrayStoreRow0" name="arrayStoreRow0" readonly="readonly"></td>
                                <td bgcolor="#F6F6F6" align="left" style="display: none"><div>current_N</div></td>
                            </tr>

                        <!-- current_N -->

             <!-- Counter starts at 1 array at 0 -->
             <!-- Counter starts at 1 array at 0 -->
             <!-- Counter starts at 1 array at 0 -->










                                <!-- THERE ARE NO DUPLICATES FOR THIS DAY -->
                                <tr id="mainRow48" class="main" myindex="48">
                            <td>
                                <div id="iconShowA48" class="ui-icon ui-icon-plusthick" onclick="showExpand('6','48')" ;=""></div>
                                <div id="iconShowB48" class="ui-icon ui-icon-minusthick" style="display: none" onclick="hideExpand('6','48')" ;=""></div>
                            </td>
                            <td align="left"></td><!-- Second icon -->
                            <td align="left">
                            <input type="checkbox" name="checkBoxSum48" id="checkBoxSum48" onclick="checkItWeek('6','48')" ;=""></td>
                            <td align="left"><div id="divWeekDTSum48"><b>10/26/2015</b></div></td>
                            <td align="left" style="display: none"></td>
                            <td align="left" style="display: none"><div id="divBusinessSum48">PL_LEGACY</div></td>
                                <td align="left" id="TDfVolumeSum48"><b>0</b></td>
                                <td align="left" id="TDfVolumeSumEdit48" style="display: none"><b><input type="text" class="allowedValues" id="fVolumeEditSum48" name="fVolumeEditSum48" value="0" onkeyup="fVolumeEditKeyUpSum(48)" ;="" size="5"> </b></td>
                            <td align="left">
                                <input type="checkbox" id="lockSum48" name="lockSum48" onclick="fVolumeEditKeyUpSum(48)" ;="">
                            </td>

Update* I like the idea of iterating. In the code, I have calculated the number of checkboxes to click, it's part of the Do While loop. Would it be possible to use On Error GoTo to skip the line n - n - 1, and increase the x value? If I can get that to work I should be able to complete the whole task. My first attempt still shoots Run-Time error '91'.

Do While n > 0
    On Error GoTo Iterate
    IE.Document.all("checkBoxSum" & x).Click

    n = n - 1
Iterate:        x = x + 7
Loop

Would help your question to include a relevant snippet of HTML from the page you're working with. — Tim Williams
– Tim Williams, Commented Sep 10, 2015 at 20:41
You could iterate increasingly one by one until IE.Document.all returns Nothing. — acelent
– acelent, Commented Sep 10, 2015 at 22:55

Tim Williams · Accepted Answer · 2015-09-14 05:36:11Z

1

This worked for me....

Const CB_NAME As String = "checkBoxSum"
Dim inputs, i, v
Dim col As New Collection

'get all of the document input elements
Set inputs = IE.document.getElementsByTagName("input")

For Each i In inputs
    'checkbox?
    If i.Type = "checkbox" Then
        'starts with "checkBoxSum" ?
        If i.Name Like CB_NAME & "*" Then
            i.Checked = True 'check it
            v = Replace(i.Name, CB_NAME, "")
            col.Add v 'store the numeric part
        End If
    End If
Next i

'show the collected values
For Each v In col
    Debug.Print v
Next v

answered Sep 14, 2015 at 5:36

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Richard Lusch Over a year ago

Awesome! This has worked out nicely. Though, I did need to change 'i.Checked = True' to 'i.Click' in order to open up the text fields for editing.

QHarr · Accepted Answer · 2018-06-30 10:37:00Z

You could also a CSS selector applied via querySelector method of document to match on elements, and return a nodeList; and a helper function to parse the associated elements' HTML for the number.

Outline code:

Option Explicit
Public Sub GetInfoSetChecks()
    'other code to browse to page etc.
    Dim aNodeList As Object, i As Long
    Set aNodeList = ie.document.querySelectorAll("input[id^='checkboxSum']")

    For i = 0 To aNodeList.Length - 1
        aNodeList.item(i).Checked = True
        ' Debug.Print aNodeList(i).Check = True ''<== sometimes this syntax instead
        Debug.Print GetCheckBoxNumber(aNodeList.item(i).outerHTML)
        'Debug.Print GetCheckBoxNumber(aNodeList(i).outerHTML)
    Next
    'Other code
End Sub

Public Function GetCheckBoxNumber(ByVal inputString As String) As Long
    GetCheckBoxNumber = Split(Split(inputString, "checkBoxSum")(1), Chr$(34))(0)
End Function

CSS selector:

input[id^='checkboxSum']

This says elements with input tag containing attribute id starting with 'checkboxSum'.

CSS query on your HTML sample:

Collectives™ on Stack Overflow

Find each instance of an HTML tag with only the partial name

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related