Click the links in an intranet table which opens another table i can scrape.

[Pip1961]

Sorry to ask for assistance but I've been trying to solve this for weeks. I have a table of date (myTable) and in the first column on each row there is a link to a sub table that I'm trying to scrape. I can collect the table data once it is displayed however i have to call a message box to pause the script while I manually click on each of the links (30+).

This is a sample of the code

    <TABLE class=mytable>

  <TBODY>
  <TR class=ScreenOnlyDispNone bgColor=silver>
  <TD style="TEXT-ALIGN: center">Shift</TD>
  <TD style="TEXT-ALIGN: center">FullName</TD>
  <TD style="TEXT-ALIGN: center">Assc</TD>
  <TD style="TEXT-ALIGN: center">Date</TD>
  <TD style="TEXT-ALIGN: center">Cases</TD>
  <TD style="TEXT-ALIGN: center">Std</TD>
  <TD style="TEXT-ALIGN: center">Act</TD>
  <TD style="TEXT-ALIGN: center">Perf</TD>
  <TD style="TEXT-ALIGN: center">Dy hh:MM:ss</TD>
  <TD style="TEXT-ALIGN: center">PickRate</TD></TR>
  <TR bgColor=lightblue>
  <TD id=tblShiftData style="WIDTH: 35px"><[B]A *******="RunRptTot '60','S'" id=linkOff href="">60</A>[/B]</TD>
  <TD id=tblFullNameData style="WIDTH: 130px"></TD>
  <TD id=tblAsscIDData style="WIDTH: 50px"></TD>
  <TD id=tblDateData style="WIDTH: 55px"></TD>
  <TD id=tblCasesData>561788</TD>
  <TD id=tblStdData>128454.98</TD>
  <TD id=tblActData>162753.19</TD>
  <TD id=tblPerfData style="WIDTH: 50px">78.93</TD>
  <TD id=tblhhMMssData style="TEXT-ALIGN: right">113d 00:33:11</TD>
  <TD id=tblPickRateData style="WIDTH: 60px">207.11</TD></TR>
  <TR bgColor=lightblue>
  <TD><[B]A *******="RunRptTot '61','S'" id=linkOff href="">61</A[/B]></TD>
  <TD></TD>
  <TD></TD>
  etc. etc.

I have managed to get the table and I have on occasion managed to click on the first link but can't get it to loop.

  Sub CycleShiftIDs()      ' Go through each shift collecting data
      Dim Table_Element As MSHTML.IHTMLElementCollection
      Dim tbl As Object, Click As Object, td As Object
      
      For Each tbl In Table_Element
          If tbl.className = "mytable" Then
          For Each td In Table_Element.getElementsByTagName("td") 
              If td.innerText = "a" Then
                  Set Click = Table_Element.getElementById("linkOff")
                  Click.Click
                  Do While IE.readyState <> 4: DoEvents: Loop
                  Call GetTableData
              End If
          Next
      Next
  End Sub

This is what I have so far, i'm hoping that I'm on the right track.

 

[John_w]

Thanks RoryA for cleaning the post.

Try this to click each link in the first column of the table:

Dim table As HTMLTable
Dim r As Long

Set table = IE.document.getElementsByClassName("mytable")(0)
For r = 1 to table.Rows.Length - 1
  table.Rows(r).Cells(0).FirstChild.Click
  Do While IE.readyState <> 4: DoEvents: Loop
  Call GetTableData
Next

IE is the InternetExplorer object loaded with the page.

 

[Pip1961]

Thanks RoryA for cleaning the post.

Try this to click each link in the first column of the table:

Dim table As HTMLTable
Dim r As Long

Set table = IE.document.getElementsByClassName("mytable")(0)
For r = 1 to table.Rows.Length - 1
  table.Rows(r).Cells(0).FirstChild.Click
  Do While IE.readyState <> 4: DoEvents: Loop
  Call GetTableData
Next

IE is the InternetExplorer object loaded with the page.

I just get runtime error 438 object doesn't support this property or method.

 

[John_w]

What is the URL?

Make sure the Click statement is referring to the A (anchor tag) element:

Debug.Print table.Rows(r).Cells(0).FirstChild.outerHTML

which outputs to the VB Immediate Window.

You might need fireEvent or dispatchEvent to click the *******part (which I assume is o_nclick without the underscore) - should be code on this forum.

However, I can't really help further without the URL.

 

[Pip1961]

What is the URL?

Make sure the Click statement is referring to the A (anchor tag) element:

Debug.Print table.Rows(r).Cells(0).FirstChild.outerHTML

which outputs to the VB Immediate Window.

You might need fireEvent or dispatchEvent to click the *******part (which I assume is o_nclick without the underscore) - should be code on this forum.

However, I can't really help further without the URL.

the URL is http://distributionsupport/dso/LF12PerfFilter.asp?DN=F5350 as I mentioned in the title it is an Intranet location. After a few trials with your code I managed to get it working today.

Sub CycleShfts()
Dim MyTable As HTMLTable
Dim r As Integer

Set MyTable = htmldoc.getElementsByTagName("table")(5)
For r = 1 To MyTable.Rows.Length - 1
Set MyTable = htmldoc.getElementsByTagName("table")(5)
MyTable.Rows(r).Cells(0).FirstChild.Click
Do While IE.readyState <> 4: DoEvents: Loop
Call GetTableData
Next
End Sub
The reason for the 'Set MyTable' appearing twice is the 'code 70 permission denied' work around. I couldn't find another way of doing it. I hae learnt a lot from just trying to resolve this one issue and if it had not been for your post I would not have managed to solve it. Thank you

 

[John_w]

Sorry, I didn't see that it was an intranet site, but glad you got it working.

Permission denied can mean that the element isn't ready or available. I normally wait for it to be ready like this:

Do
  Set MyTable = htmldoc.getElementsByTagName("table")(5)
  DoEvents
Loop While MyTable Is Nothing

Or this:

Dim ready As Boolean
ready = False
Do
  Set MyTable = htmldoc.getElementsByTagName("table")(5)
  If Not MyTable Is Nothing
    If MyTable.Rows.Length > 1 Then ready = True
  End If
  DoEvents
Loop Until ready

Either of the above bits of code replace the first Set MyTable in your code, and the second Set MyTable should not be needed.