How to check a checkbox in a web page which has a dynamic values , Using Excel VBA?

Question

I have seen tutorials it is very informative and very easy to understand.

I have doubt in clicking a check box in a website using EXCEL VBA the check box values will be dynamic.It seems to be very challenging for me. If someone helps me to fix this i will feel awesome. I have mentioned below the HTML elements for your reference.

Requirement is to select qualifer code (2015, 2018 & KD) and delete.

Sub Editing_APA_Qualifiers()

Dim IE As Object Dim Doc As HTMLDocument Set IE = CreateObject("InternetExplorer.Application") Dim SHELL_OBJECT SHELL_OBJECT = "WScript.Shell" Set objShell = CreateObject(SHELL_OBJECT)

IE.Visible = True

'navigate URL

IE.navigate "URL"

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Set Doc = IE.document

' Set numrows = number of rows of data.
  NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count

  ' Establish "For" loop to loop "numrows" number of times.
  For intRow = 2 To NumRows

  IE.Visible = True

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Doc.getElementById("txtTimeStudyNbr").Value = ThisWorkbook.Sheets("data").Range("A" & intRow).Value
Doc.getElementById("Search").Click

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Doc.getElementsByName("chkDel").Value = ThisWorkbook.Sheets("data").Range("B" & intRow).Value(0).Click
Doc.getElementsByName("chkDel").Value = ThisWorkbook.Sheets("data").Range("C" & intRow).Value(0).Click
Doc.getElementsByName("chkDel")(7).Click
Doc.getElementById("Delete").Click
objShell.SendKeys "{Enter}"

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Doc.getElementById("lstQualifierTypes").Value = ThisWorkbook.Sheets("data").Range("E1").Value
Doc.getElementById("Search").Click

Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop

Doc.getElementById("lstQualifiers").Value = ThisWorkbook.Sheets("data").Range("E2").Value
Doc.getElementById("ADD").Click

So in these how we can select the check boxes.

For your easy reference
<input type="checkbox" name="chkDel" value="0|MY|2018~"></td>
<input type="checkbox" name="chkDel" value="1|MY|2017~"></td>
<input type="checkbox" name="chkDel" value="2|MY|2016~"></td>
<input type="checkbox" name="chkDel" value="3|MY|2015~"></td>
<input type="checkbox" name="chkDel" value="4|VT|L~"></td>
<input type="checkbox" name="chkDel" value="5|VL|KD~"></td>
<input type="checkbox" name="chkDel" value="6|VL|K#~"></td>

<tbody><tr style="background-color:#ada5bd">
                                <th>&nbsp;</th>
			        <th class="center">Del</th>
				<th class="center">Qualifier Type</th>
				<th class="center">Qualifier Code </th>
				<th class="center">Abbreviation </th>
				<th class="center">Print </th>
				<th class="center">Rev </th>
				<th class="center">User Id </th>
				<th class="center">Date </th></tr>

<!-- Printing -->
<!-- QT="MY" QC="2018" -->
<!-- UpdateId:VKUMARAG -->
<input type="hidden" name="QualifierType0" value="MY">
<input type="hidden" name="QualifierCode0" value="2018">
<input type="hidden" name="Print0" value="Y">
<tr id="tr0"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(0)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="0|MY|2018~"></td>
<td class="center">&nbsp;
MY</td>
<td class="center">&nbsp;
2018</td>
<td class="left">&nbsp;
Eff: Ineff:  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
VKUMARAG</td>
<td class="left">&nbsp;
06-mar-2018</td>
</tr>

<!-- Printing -->
<!-- QT="MY" QC="2017" -->
<!-- UpdateId:VKUMARAG -->
<input type="hidden" name="QualifierType1" value="MY">
<input type="hidden" name="QualifierCode1" value="2017">
<input type="hidden" name="Print1" value="Y">
<tr id="tr1"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(1)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="1|MY|2017~"></td>
<td class="center">&nbsp;
MY</td>
<td class="center">&nbsp;
2017</td>
<td class="left">&nbsp;
Eff: Ineff:  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
VKUMARAG</td>
<td class="left">&nbsp;
27-may-2016</td>

</tr>

<!-- Printing -->
<!-- QT="MY" QC="2016" -->
<!-- UpdateId:ACYRUS3 -->
<input type="hidden" name="QualifierType2" value="MY">
<input type="hidden" name="QualifierCode2" value="2016">
<input type="hidden" name="Print2" value="Y">
<tr id="tr2"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(2)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="2|MY|2016~"></td>
<td class="center">&nbsp;
MY</td>
<td class="center">&nbsp;
2016</td>
<td class="left">&nbsp;
Eff: Ineff:  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
ACYRUS3</td>
<td class="left">&nbsp;
17-nov-2015</td>
</tr>

<!-- Printing -->
<!-- QT="MY" QC="2015" -->
<!-- UpdateId:APIMPORT -->
<input type="hidden" name="QualifierType3" value="MY">
<input type="hidden" name="QualifierCode3" value="2015">
<input type="hidden" name="Print3" value="Y">
<tr id="tr3"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(3)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="3|MY|2015~"></td>
<td class="center">&nbsp;
MY</td>
<td class="center">&nbsp;
2015</td>
<td class="left">&nbsp;
Eff: Ineff:  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
APIMPORT</td>
<td class="left">&nbsp;
20-sep-2013</td>
</tr>
<!-- Printing -->
<!-- QT="VT" QC="L" -->
<!-- UpdateId:APIMPORT -->
<input type="hidden" name="QualifierType4" value="VT">
<input type="hidden" name="QualifierCode4" value="L">
<input type="hidden" name="Print4" value="Y">
<tr id="tr4"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(4)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="4|VT|L~"></td>
<td class="center">&nbsp;
VT</td>
<td class="center">&nbsp;
L</td>
<td class="left">&nbsp;
Truck  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
APIMPORT</td>
<td class="left">&nbsp;
20-sep-2013</td>
</tr>
<!-- Printing -->
<!-- QT="VL" QC="KD" -->
<!-- UpdateId:VKUMARAG -->
<input type="hidden" name="QualifierType5" value="VL">
<input type="hidden" name="QualifierCode5" value="KD">
<input type="hidden" name="Print5" value="Y">
<tr id="tr5"><td width="1">	<input type="radio" name="rdOperation" onclick="return radio_onclick(5)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="5|VL|KD~"></td>
<td class="center">&nbsp;
VL</td>
<td class="center">&nbsp;
KD</td>
<td class="left">&nbsp;
RANGER ICA  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
VKUMARAG</td>
<td class="left">&nbsp;
23-apr-2020</td>
</tr>
<input type="hidden" name="QualifierType6" value="VL">
<input type="hidden" name="QualifierCode6" value="K#">
<input type="hidden" name="Print6" value="Y">
<tr id="tr6"><td width="1">	<input type="radio" name="rdOperation" language="javascript" onclick="return radio_onclick(6)"> </td>
<td width="1">
<input type="checkbox" name="chkDel" value="6|VL|K#~"></td>
<td class="center">&nbsp;
VL</td>
<td class="center">&nbsp;
K#</td>
<td class="left">&nbsp;
RANGER OLD  </td>
<td class="center">&nbsp;
Y</td>
<td class="center">&nbsp;
A</td>
<td class="left">&nbsp;
APIMPORT</td>
<td class="left">&nbsp;
20-sep-2013</td>
</tr>

 </tbody>

QHarr QHarr · Accepted Answer · 2020-04-25T19:26:54

Try using css attribute = value selectors with contains operator (*). That way you can specify the static part of attribute value to match on, along with the node type attribute.

E.g.

ie.document.querySelector("[type=checkbox][value*='0|MY|2018']").click

Here I assume that 0|MY|2018 is static within the target node.

How to check a checkbox in a web page which has a dynamic values , Using Excel VBA?

1 Answers