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 · Accepted Answer · 2020-04-25 19:26:54Z

0

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.

answered Apr 25, 2020 at 19:26

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

4 Comments

Vikneshwar Mk Over a year ago

No these values and index will vary in each cases. In the above yellow box I have mentioned how Excel input files looks and three scenarios below that I have mentioned the requirements too. In 1st scenario value = '0|MY|2018'. ,2nd scenario='0|MY|2017'. ,3RD Scenario ='0|MY|2016'.

Vikneshwar Mk Over a year ago

Also please see the attached image how it looks

QHarr Over a year ago

Use the same principle to select each of the other check boxes as appropriate. I'm finding it hard to follow your explanation of what is required in the question. It should however, still boil down to the same syntax when making a selection.

Vikneshwar Mk Over a year ago

I Have updated the images for the better understanding . Can any one assist me on this.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related