Web scraping tag name

serhito

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am trying to retrieve data from a table of a website, but I am stuck getting the correct tagname.
Website :


Here's what I wrote so far :

VBA Code:
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 :

zacks.JPG


Exact info I need for each tr.
zacks2.JPG


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


Any help is well appreciated !
Thanks
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Which of the 30 tables are you interested in?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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 :

VBA Code:
 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

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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"
1.JPG


VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,462
Messages
5,636,419
Members
416,917
Latest member
koto1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top