Web scraping drop down list

serhito

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have this procedure to retrieve data from a website into Excel.
I can retrieve the info, but I need to select a drop down list to value 100.
I have tried lots of different possibilities without success.
It's the line with " ieObj.document.getElementById("earnings_announcements_earnings_table_length").Value = "100""
Anybody could point out the obvious that I might be missing ?
Thanks a lot.

VBA Code:
Sub Zackstesting()
    ' declare the variables
    Dim ieObj As InternetExplorer
    Dim htmlEle As IHTMLElement
    Dim i As Integer
    Dim List As Object
    Dim ie As Object
  
    ' initialize i to one
    i = 1
  
    ' create and get access to an instance of IE
    Set ieObj = New InternetExplorer
    ieObj.Visible = False
    ieObj.navigate "https://www.zacks.com/stock/research/AAPL/earnings-announcements"
  
    ' give the webpage some time to load all content
    Application.Wait Now + TimeValue("00:00:05")
    
    ieObj.document.getElementById("earnings_announcements_earnings_table_length").Value = "100"
    
  
  
    ' loop through all the rows in the table
    For Each htmlEle In ieObj.document.getElementById("display dataTable no-footer")(1).getElementsByTagName("tr")
        With ActiveSheet
            .Range("A" & i).Value = htmlEle.Children(0).textContent
            .Range("B" & i).Value = htmlEle.Children(1).textContent
            .Range("C" & i).Value = htmlEle.Children(2).textContent
            .Range("D" & i).Value = htmlEle.Children(3).textContent
            .Range("E" & i).Value = htmlEle.Children(4).textContent
            .Range("F" & i).Value = htmlEle.Children(5).textContent
            .Range("G" & i).Value = htmlEle.Children(6).textContent
            End With
        i = i + 1
    Next htmlEle
  
End Sub

zacks4.JPG
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
What's happening on that line?

Are you getting errors?

The value isn't being selected?

The value is being selected but it's not triggering any action?
 

serhito

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Error.jpg


without that line, I can get the data, but only 10 which is the default.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
There's a problem with the page, they've used the same ID more than once.

The code you posted returns the first element with the ID 'earnings_announcements_earnings_table_length' which is a DIV, not the SELECT you want.

You can try this, but please note it is the page that is wrong - you should never use the same ID for more than one element.
VBA Code:
Option Explicit

Sub Zackstesting()
    ' declare the variables
    Dim ieObj As InternetExplorer
    Dim htmlEle As Object
    Dim i As Integer
    Dim List As Object
    Dim ie As Object
  
    ' initialize i to one
    i = 1
  
    ' create and get access to an instance of IE
    Set ieObj = New InternetExplorer
    ieObj.Visible = False
    ieObj.navigate "https://www.zacks.com/stock/research/AAPL/earnings-announcements"
  
    ' give the webpage some time to load all content
    Application.Wait Now + TimeValue("00:00:05")
    
    Set List = ieObj.document.getElementById("earnings_announcements_earnings_table_length").Children(0).Children(0)
    List.Value = "100"
  
    ' loop through all the rows in the table
    For Each htmlEle In ieObj.document.getElementById("display dataTable no-footer")(1).getElementsByTagName("tr")
        With ActiveSheet
            .Range("A" & i).Value = htmlEle.Children(0).textContent
            .Range("B" & i).Value = htmlEle.Children(1).textContent
            .Range("C" & i).Value = htmlEle.Children(2).textContent
            .Range("D" & i).Value = htmlEle.Children(3).textContent
            .Range("E" & i).Value = htmlEle.Children(4).textContent
            .Range("F" & i).Value = htmlEle.Children(5).textContent
            .Range("G" & i).Value = htmlEle.Children(6).textContent
            End With
        i = i + 1
    Next htmlEle
  
End Sub
[/vba]
 

serhito

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I see what you are saying. Thank you so much for pointing that out.
I have a very basic question. Is there a way to run the vba and watch live in the html code what is happening ? I would like to learn how to troubleshoot without just waiting for the error to popup.
In this instance it is still returning the default selection (10) instead of the desired (100).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Make IE visible and you can see what's happening as you step through the code.
 

serhito

New Member
Joined
Jun 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Update : it actually select the 100, but it doesn't load the data. I have added an instruction to give it time to load it, but that still doesn't do it.
Application.Wait Now + TimeValue("00:00:10")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
The dropdown has an event listener attached to it that, unfortunately, isn't being triggered when you change the value via code.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,618
Messages
5,625,888
Members
416,141
Latest member
Bartek9q

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