Updating dropdown menu item on web page

[markb]

New to this forum, and would appreciate any help with using VBA to update a dropdown menu on a web page. I have scoured the internet without success.

I have written some code to download Excel files from a Medicare website. It contains links to several Excel files, and my code will read the links, download the files, process them in Excel and then import them into the website. The problem is that on that site there is a dropdown menu for the user to choose how many files to list (10, 25, 50 or 100). The default is 10, and I want to get all 100. I can modify the value of the dropdown menu (see my code below), but I can't figure out how to get the page to update. There is no "Go" button, and the page that lists 100 files does not have a unique URL.

Here is my code. If you access the page and change the dropdown menu, you will see that it automatically updates the page. How can I accomplish that with VBA? I am using IE 8, and I have tried using .FireEvent("onchange") and .FireEvent("onclick") without success. *** I seem unable to post the link because I have not yet posted at least five times to the site. Not sure how to solve this one......****


Public Sub GetFiles()

Dim MyURL As String
MyURL = ""
Set MyBrowser = New InternetExplorer
With MyBrowser
.Silent = True
.navigate MyURL
.Visible = True
Do
DoEvents
Loop Until .readyState = READYSTATE_COMPLETE

Set HTMLDoc = .Document
HTMLDoc.getElementById("selectNumOfRows").Value = "100"

End With

End Sub

[markb]

I'll try a workaround.....here it is without the leading "www"

cms.gov/Medicare/Medicare-Fee-for-Service-Payment/PhysicianFeeSched/PFS-Relative-Value-Files.html

[jonh]

Why do you need to manipulate the page? All the links are listed in the page source code.

[markb]

Thanks for your repoly, but I'm not following you. Maybe I'm missing something in the source code for the page, but as far as I can tell when the page loads it has only 10 hyperlinks. To get the other 90 hyperlinks "100" needs to be selected in the dropdown menu. I can't figure out how to do this and have the page update programmatically so that my code can automatically capture all those hyperlinks and then download and process the files.

[markb]

I found code that works using .createEvent


Public Sub GetFiles()

Dim MyURL As String
Dim evt As Object
Dim lst As Object

MyURL = ""
Set MyBrowser = New InternetExplorer
With MyBrowser
.Silent = True
.navigate MyURL
.Visible = True
Do
DoEvents
Loop Until .ReadyState = READYSTATE_COMPLETE

Set evt = .Document.createEvent("HTMLEvents")
evt.initEvent "change", True, False
Set lst = .Document.getElementById("selectNumOfRows")
lst.selectedIndex = 3
lst.dispatchEvent evt

End With

End Sub

[markb]

This is adapted from code at xtremevbtalk.com/showthread.php?t=325370

[markb]

ooops! Works in IE11 but not IE8.
Anyone know what would work in IE8?

[jonh]

Maybe we're looking at different things because in the page you linked above there is no element called 'selectNumOfRows'.
On that page, above the page selectors it says "Showing 1 to 10 of 58 entries".
View the source html and search for aaSorting.
Above that piece of javascript is a table that contains the 58 urls.

It seems to use script to sort and filter that table. That's why there is no submit, because the page doesn't need to reload. It already contains all the data it needs.

[markb]

Got it. Thanks. I was using the "Inspect Element" function to identify the ID and tag for the dropdown box. That is how I found "selectNumOfRows". The source code is completely different, and I can see where the hyperlinks are listed. I had to do some research to understand the difference between the source code and elements, and I'm not sure I do. This leaves me wondering how to extract each of those hyperlinks because I don't see a tag or ID for them the way I do when I use "inspect element". I am also still wondering if it is possible to use .createEvent or .createEventObject in IE8

[jonh]

Well the trouble with manipulating objects through the DOM is that, as you say, things can change between versions.
Web pages are just plain text, no matter what browser or version you're using, so if you can grab the source and extract what you need from that you'll probably be better off.

You can read stuff directly using XMLHTTP.

This should get you your urls.


Sub test()
Dim itm As Variant, ary() As String
ary = GetUrls("http://www.xyz.htm")
For Each itm In ary
If itm <> "" Then Debug.Print itm
Next
End Sub

Function GetUrls(URL As String) As String()
Dim s As String, ipos As Long, iend As Long, ary() As String
Const find As String = "scope=""row""><a href="""
s = FetchPage(URL)
If s <> "" Then
iend = 1
ReDim ary(0)
Do
ipos = InStr(iend, s, find, vbTextCompare) + Len(find)
If ipos = 0 Or ipos < iend Then Exit Do
iend = InStr(ipos, s, """", vbTextCompare)
If iend = 0 Then Exit Do
ary(UBound(ary)) = Trim(Mid(s, ipos, iend - ipos))
ReDim Preserve ary(UBound(ary) + 1)
Loop
GetUrls = ary
End If
End Function

Function FetchPage(URL)
With CreateObject("Microsoft.XMLHTTP")
.Open "GET", URL, False
.send
FetchPage = .responseText
End With
End Function

[markb]

Perfect! This is exactly what I was trying to do. You have saved me countless, probably unproductive, hours of surfing the web for a solution and taught me some important new knowledge. Thanks very much!