Clicking Table Row in HTML Table using VBA

Question

I am attempting to create an automation tool in Excel using VBA which runs and downloads a database query from a website portal. The tool is almost completely functional, but the one step I just cannot figure out is the query selection.

Once the user has searched for the query in the portal, applicable queries are put into rows within a HTML table. There are two ways a user can run a query: double-click a row OR click a row and then click another button in the portal. My difficulty is that I am unable to preform either of these tactics even after hours of research.

The HTML code for the first row of the table (which is the desired query) is seen below. I included the td tags so you could also see the cells within the row.

<TABLE class="x-grid-table x-grid-table-resizer" style="WIDTH: 1867px" cellSpacing=0 cellPadding=0 border=0>
    <TBODY>
        <TR class="x-grid-row " _nodup="30806" viewIndex="0" viewRecordId="ext-record-17" boundView="objectgridview">
            <TD class=" x-grid-cell x-grid-cell-gridcolumn-1085   x-grid-cell-first" rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">
                    <SPAN class="grid-icon view-16"></SPAN>
                    <SPAN>QUERYNAME</SPAN> 
                </DIV>
            </TD>
            <TD class=" x-grid-cell x-grid-cell-gridcolumn-1086   " rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">QUERYTYPE</DIV></TD>
            <TD class=" x-grid-cell x-grid-cell-gridcolumn-1087   " rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">QUERYDESCRIPTION</DIV>
            </TD>
            <TD class=" x-grid-cell x-grid-cell-gridcolumn-1088   " rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">QUERYLIBRARY</DIV>
            </TD>
            <TD class=" x-grid-cell x-grid-cell-gridcolumn-1089   " rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">QUERYOWNER</DIV>
            </TD>
            <TD class=" x-grid-cell x-grid-cell-datecolumn-1090    x-grid-cell-last" rowSpan="1" colSpan="1">
                <DIV class="x-grid-cell-inner " style="TEXT-ALIGN: left">QUERYDATE</DIV>
            </TD>     
        </TR>
    </TBODY>
</TABLE>

I also looked at the HTML in Chrome, with the only other difference being that the tr classname switches to "x-grid-row x-grid-row-over" when the mouse cursor is over that row and "x-grid-row x-grid-row-selected x-grid-row-over-focused" when the row is clicked.

As mentioned I have tried a number of methods to click on this row. I was able to use getElementsByTagName is identify the row/table/others element, but even after finding this I still couldn't find success.

Some of the code lines that I have used are below.

IE.document.getElementsByTagName("tr")(42).Classname = "x-grid-row x-grid-row-selected x-grid-row-focused"
IE.document.getElementsByTagName("table")(20).Rows(0).Click
IE.document.getElementsByTagName("tr")(42).getElementsByTagName("td")(3).getElementsByTagName("div")(0).Click
IE.document.getElementsByTagName("tr")(42).getElementsByTagName("td")(3).Selected = True
Call IE.document.getElementsByTagName("tr")(42).execScript("mouseup()", "Javascript")

Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys ("-{F10}")
WshShell.SendKeys "{DOWN}"
WshShell.SendKeys "{ENTER}"

I am hoping someone might be able to provide some knowledge that I am missing. Thanks!

Mitch · Accepted Answer · 2018-01-13 16:52:17Z

1

I'm not really sure whether this applies to your situation, but I've been dealing with the same issues in kendo-ui. I use the following code to select the first row in a kendoGrid.

       ie.Document.parentWindow.execScript "$('#drgdLease').data('kendoGrid').select('tr:eq(6)');"

Look further up in your server code, above the "table" tag, for an "ID", maybe as far up as the "div" element, and try something like:

       ie.Document.parentWindow.execScript "$('#yourId').data('x-grid').select('tr:eq(42)');"

I think your best option is to figure out to make "execScript" work. It took me a long time to get that one line, and I had help ! Good Luck

answered Jan 13, 2018 at 16:52

Mitch

5833 silver badges13 bronze badges

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

2 Comments

dtellez22 Over a year ago

Thanks Mitch! Without knowing anything about kendo-ui, I gave this the ol' college try. I went up the code to the closest div element are found the element you mentioned. The codes I used were either ie.document.parentWindow.execScript "$('#objectgridview').data('x-grid-row').select('tr:eq(42)') or ie.document.parentWindow.execScript "$('#objectgridview').data('x-grid').select('tr:eq(42)'). Unfortunately neither method worked as I just got an error which read: Could not complete the operation due to error 80020101.

Mitch Over a year ago

@dtellez22 Good deal on the error code (believe it or not). It means you are on the right track, just the syntax or object is wrong. That's an error code for "automation". It took me forever trying different combinations before I stumbled across the right one. I wish I could help you a little more. Perhaps if you could post the server code from the "div" element down, I might be able to come up with something. Also look for the <script> tag and expand it to see what the script is doing. Good Luck

QHarr · Accepted Answer · 2018-10-02 08:36:00Z

1

Did you try using combination of attribute selectors with FireEvent?

ie.document.querySelector("[_nodup='30806'][viewindex='0']").FireEvent "ondblclick"

This targets the first row and use a double click. Do those attributes also change?

answered Oct 2, 2018 at 8:36

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Comments

K.Dᴀᴠɪs · Accepted Answer · 2018-01-15 22:02:45Z

0

I have never attempted to double click, so if this doesn't work we may need to replace the second .click to click the other element on your webpage.

This code should iterate through ALL the rows on the table. You can modify if needed.

Code:

Option Explicit

Sub GrabRows()

    Dim ie As InternetExplorer, doc As HTMLDocument

    'Ensure you have code in place to navigate and
    'ensure the page is fully loaded here.

    Dim myTbl As HTMLTable, tRows As HTMLTableRow, tRow As HTMLTableRow

    Set doc = ie.document
    Set myTbl = doc.getElementsByClassName("x-grid-table x-grid-table-resizer")(2)
    Set tRows = myTbl.getElementsByTagName("TR")(2)

    '____Save as a backup____
    'For Each tRow In tRows
    '    tRow.Click
    '    tRow.Click
    'Next tRow
    '------------------------

    'Try each of these together and see if you get desired result
    tRow.Click
    tRow.setActive
    tRow.Focus


End Sub

One of two things needs to happen. You need to make references to Microsoft Internet Controls and Microsoft HTML Object Library, or declare all of the library-specific objects (such as InternetExplorer & HTMLDocument) as type Object

edited Jan 15, 2018 at 22:02

answered Jan 12, 2018 at 20:51

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

5 Comments

dtellez22 Over a year ago

So I actually utilized a similar method before: I was able to find the element using either getElementsbyClassName or similar methods and then simply added .Click. For the row I want to click on, this comes out to be: doc.getElementsByClassName("x-grid-table x-grid-table-resizer")(2).getElementsByTagName("TR")(2).Click However, when this is adjudicated, nothing actually happens on the screen. I know I can click on the row as I do it regularly with my mouse, but I am wondering if .Click is the way to preform this function when trying to automate the process for this table and webpage.

K.Dᴀᴠɪs Over a year ago

Yeah without seeing the full html code it would be difficult for me to select the correct instance of your table. But Happy to see that you found the solution!

dtellez22 Over a year ago

Hi K.Davis, I updated the comment to include the difficulty I am still having with the click event. Please see the above updated comment and let me know what you think. Thanks!

K.Dᴀᴠɪs Over a year ago

@dtellez22 This might be a bit overkill, but you can try to .Click first, then .setActive, followed by .Focus. I will update my code (along with your (2) that you determined to be the correct instances).

dtellez22 Over a year ago

Unfortunately that did not work either. Even after implementing these other methods, the screen still stays absent of change. I have made progress only one way: by switching the classname of the TR element to 'x-grid-row x-grid-row-selected x-grid-row-focused'. I noticed that is what the classname changed to when I manually clicked the row in Chrome. In IE this code highlighted the TR in question (thus I know I am calling the correct element). However, this merely highlights the row and does not appear to activate it like a manual click does (the secondary button does not become active).

Collectives™ on Stack Overflow

Clicking Table Row in HTML Table using VBA

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related