Web scraping drop down list

[serhito]

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.

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

 

[Norie]

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]

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

 

[Norie]

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.

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]

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]

Make IE visible and you can see what's happening as you step through the code.

 

[serhito]

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]

The dropdown has an event listener attached to it that, unfortunately, isn't being triggered when you change the value via code.

 

[serhito]

I figured it out with this explanation.
VBA: How to get page to update after filling in the select box

Thanks for your help !!