Feed value to dropdown menu within Internet Explorer page

[MacroMax]

Hello everyone,
Bear with me as this is my first use of a forum!
My goal is to use VBA to login to a secure webpage and copy/paste the data from the main table on the page. The code works perfectly except for one thing - the website's default is to show 10 items per page. I am looking to grab 55 items and so if I was navigating the page by hand I would scroll to the bottom of the page and select view 100 items per page. In this way I can essentially copy and paste all of the records in one go. Bottom line is that I'd like my VBA code to select this '100 items per page' option and I'm completely lost as to how to accomplish this task.

In my search I've come across getelementsbyid and getelementsbyclassname, but I'm still unable to get this section of my code working.

Here is the HTML code from the website that holds the dropdown menu:

<span tabindex="0" class="k-widget k-dropdown k-header very-short" role="listbox" aria-busy="false" aria-disabled="false" aria
-expanded="false" aria-haspopup="true" aria-readonly="false" aria-owns="" unselectable="on">
<span class="k-dropdown-wrap k-state-default" style="width: 40px;" unselectable="on">
<span class="k-input" unselectable="on">10</span>
<span class="k-select" unselectable="on"><span class="k-icon k-i-arrow-s" unselectable="on">select</span>
</span>
</span>
<select style="display: none;" data-role="dropdownlist">
<option selected="selected" value="10">10</option>
<option value="25">25</option>
<option value="50">50</option>
<option value="100">100</option>
</select>
</span>

I'm not sure how to decipher this but I can match the 10, 25, 50, and 100 as the dropdown menu options.

I hope this information isn't too vague. Thank you to anyone who can help or point me in the right direction!

 

[V_Malkoti]

If its the only select tag on this page then this might work:

Sub htmlTest()
    Dim objIE, listCbo, c


    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.Navigate "[COLOR=#0000ff]C:\TestingResults\Code files\codetest\cbotest.html[/COLOR]"


    While objIE.Busy
        Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 2)
    Wend
    
    Set listCbo = objIE.document.getElementsByTagName("select")
    For Each c In listCbo
        c.Value = "100"
    Next
End Sub

 

[MacroMax]

Thank you very much for your response!
I think that got me most of the way there, but there is something buggy going on with the website - I will do my best to describe the issue.

Any time I have visited this website (navigating manually not by code) I've experienced the same small glitch. When I click on the drop down menu to select 'view 100 items per page' the menu extends and then quickly retracts. Only when I click on the drop down menu the second time does the menu extend and and stay extended so that I may choose an option.

What I've done is add the code you've provided followed by the line objIE.Refresh And I've placed a break point on this refresh line.

The code runs to the break point, I manually click the drop down menu so that it can do its glitchy extend/retract thing, and then resume running the code. It works but I'm wondering if you have any thoughts as to how I might do this manual clicking via VBA.

Thanks!

 

[V_Malkoti]

What happens if you don't click on it at all when running the code? Do you get any error, or does the selection not work if the control has not been clicked before changing the value?

When you are using code to select a value from drop-down, then there should be no need to click on it to show the list of options in it. Unless code to load items is tied to mouse/keyboard events on drop-down.