Question : I'm trying to remove rows from a html table, if the 'tr' elements have 'th' elements as children. The code below, removes any such rows based on the aforementioned condition but only for Page 1. How can I keep macro listen whenever the Page changes and trigger the code, if that happens? The macro should stop only when a user closes the webpage.
Code
Sub SearchAndGetCompanyList()
Dim IE As SHDocVw.InternetExplorer
Dim Sh As Worksheet
Dim URL As String
Dim SUrl, DUrl, RUrl As MSHTML.HTMLDocument
Dim Login, RunThisQuery As MSHTML.HTMLButtonElement
Dim Table As MSHTML.HTMLTable
Dim TableRowCollection As MSHTML.IHTMLElementCollection
Dim TableCellToKeep As MSHTML.HTMLTableCell
Dim TableCellToRemove As MSHTML.HTMLTableCell
Dim TableCellCollection As MSHTML.IHTMLElementCollection
Dim UlPagination As MSHTML.HTMLUListElement
Dim LiPagination As MSHTML.HTMLLIElement
Dim CompanyToAdd As MSHTML.HTMLTableCell
Dim a, b, c As Long
URL = "https://www.screener.in/login/"
Set Sh = ActiveWorkbook.ActiveSheet
' Debug.Print Sh.Type
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate URL
IE.Visible = True
' Wait till the Internet Explorer has finished loading
Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop
' Stores a reference to the HTML Document in that variable
Set SUrl = IE.Document
' Wait for document to load
If SUrl.ReadyState = "complete" Then
SUrl.getElementsByTagName("input")(1).Value = "[email protected]"
SUrl.getElementsByTagName("input")(2).Value = "screener12345"
Set Login = SUrl.getElementsByClassName("btn btn-primary").Item(0)
Login.Click
Application.Wait (Now + TimeValue("00:00:05"))
Set DUrl = IE.Document
DUrl.getElementsByClassName("form-control")(1).Value = "Price to earning < 15 AND Return on capital employed > 20%"
Application.Wait (Now + TimeValue("00:00:05"))
Set RunThisQuery = DUrl.getElementsByClassName("btn btn-primary").Item(2)
RunThisQuery.Click
Application.Wait (Now + TimeValue("00:00:05"))
Set RUrl = IE.Document
' Debug.Print RUrl.Location
Set TableRowCollection = RUrl.getElementsByTagName("table").Item(0).getElementsByTagName("tr")
Debug.Print TableRowCollection.Length
For a = 1 To TableRowCollection.Length - 1
If a = 51 Then
Exit For
End If
Set TableCellToKeep = RUrl.getElementsByTagName("table").Item(0).getElementsByTagName("tr").Item(a).getElementsByTagName("td").Item(1)
If Not TableCellToKeep Is Nothing Then
Debug.Print "Keep" & " " & a & " " & TableCellToKeep.innerText
GoTo NextIteration
Else
Set TableCellToRemove = RUrl.getElementsByTagName("table").Item(0).getElementsByTagName("tr").Item(a).getElementsByTagName("th").Item(1)
' Debug.Print TableCellToRemove.innerHTML
If TableCellToRemove.ParentNode.tagName = "TR" Then
TableCellToRemove.ParentNode.removeNode (True)
End If
Debug.Print "Remove" & " " & a
a = a - 1
End If
NextIteration:
Next
End If
End Sub