Web scraping tag name

[serhito]

Hi,
I am trying to retrieve data from a table of a website, but I am stuck getting the correct tagname.
Website :

What date does Apple's (AAPL) report Earnings - Earnings Calendar & Announcement

Learn more about Apple's (AAPL) upcoming earnings announcement. Get Earnings Calendar and Data from Zacks.com

www.zacks.com

Here's what I wrote so far :

Public Sub Zacks()
    Dim html As MSHTML.HTMLDocument, hTable As Object, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set html = New MSHTML.HTMLDocument                  '<  VBE > Tools > References > Microsoft Scripting Runtime
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.zacks.com/stock/research/AAPL/earnings-announcements", False
        .send
        html.body.innerHTML = .responseText
    End With
    Set hTable = html.querySelector("earnings_annoucements_earnings_table")
    Dim td As Object, tr As Object, th As Object, r As Long, c As Long
    For Each tr In hTable.getElementsByTagName("tr")
        r = r + 1: c = 1
        For Each th In tr.getElementsByTagName("th")
            ws.Cells(r, c) = th.innerText
            c = c + 1
        Next
        For Each td In tr.getElementsByTagName("td")
            ws.Cells(r, c) = td.innerText
            c = c + 1
        Next
    Next
End Sub

and this is the website details :

Exact info I need for each tr.

Also, the table on the website, has the option to select 100 items to display, how can I select that in my macro ?

Any help is well appreciated !
Thanks

 

[Norie]

Which of the 30 tables are you interested in?

 

[serhito]

Which of the 30 tables are you interested in?

the one called "earnings_annoucements_earnings_table". I believe it's number 4

 

[serhito]

anyone ?

 

[Norie]

Instead of using queryselector with the ID of the table use queryselectorAll("table") to get all the tables then loop through to find the one you want by checking the id.

 

[serhito]

I found which one, I am learning how to do this so the part I am not sure is the looping part, which tagname and instruction to use to retrieve the data.
This part :

 For Each tr In hTable.getElementsByTagName("tr")
        r = r + 1: c = 1
        For Each th In tr.getElementsByTagName("th")
            ws.Cells(r, c) = th.innerText
            c = c + 1
        Next
        For Each td In tr.getElementsByTagName("td")
            ws.Cells(r, c) = td.innerText
            c = c + 1
        Next
    Next

 

[Norie]

Somewhere I have a bunch of code for extracting data from HTML tables.

If you post your current code, i.e. the one that's actually getting the table, I'll see if I can find that code and post it.

 

[serhito]

Look I am really sorry, and I don't want you to think that I am lazy and don't want to do the work.
I just don't do this for a living, but I want to learn how to do it. I just spent another 2 hours trying to make this run.
I am not sure what you are asking me to do.
So here's what I have so far. I know it's table 4 called "earnings_announcements_earnings_table"

Option Explicit
Public Sub Zacks5()
    Dim html As MSHTML.HTMLDocument, hTable As Object, ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set html = New MSHTML.HTMLDocument                  '<  VBE > Tools > References > Microsoft Scripting Runtime
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.zacks.com/stock/research/AAPL/earnings-announcements", False
        .send
        html.body.innerHTML = .responseText
    End With
    Set hTable = html.querySelector("earnings_announcements_earnings_table")
    Dim td As Object, tr As Object, th As Object, r As Long, c As Long
   
 For Each tr In hTable.getElementsByTagName("tr")
        r = r + 1: c = 1
        
     For Each td In tr.getElementsByTagName("td")
            ws.Cells(r, c) = td.innerText
            c = c + 1
        Next
    Next
End Sub

Thanks for your patience and help.

 

[Norie]

Sorry, I'm only trying to help.

One major issue with that is you've not actually mentioned what problems you are having.

Are you getting errors?

Unexpected results?

No results?

 

[serhito]

All of the above !! ?
I am getting a error 91. Object variable or with block variable not set.
Looks to me like all my variables are set.
But again, I have very limited knowledge of vba language. I can work with formulas, etc... but VBA is tough for me.
I just need to be able to pull that info, but the table won't show up with a normal query.
I have looked at pages and pages of examples, and it seems that I am doing the same thing, but no luck.
The problem as a newbie, is that you never know if it's even possible to retrieve the data.
Anyway, I appreciate that you are helping.