Search code examples
vbaweb-scrapingautomation

VBA to change dropdown value in internet explorer

I am looking to automate internet explorer using Excel VBA to extract football results from a website and am really struggling with getting the data to update when I change the dropdown value.

The website is: http://www.whoscored.com/Regions/250/Tournaments/30/Seasons/3871/Stages/8209/Fixtures/Europe-UEFA-Europa-League-2013-2014

I am looking to change the value of the 'stages' dropdown and scrape the match results.

My code works fine for opening IE, changing the value of the 'scrape' dropdown but I can't get the data to update. Whilst I am comfortable with VBA I know very little about HTML and Javascript although I can guess what some lines are doing. From what I can see there is javascript code that handles the change event, I just can't see how to get it to run - I have tried firing the 'onchange' event in my code as suggested from my searches but I can't get it to work.

This is the code I can see that controls the drop down (I have deleted a lot of the dropdown values for other dropdowns as it made this post even longer:

<div id="breadcrumb-nav">
.
.
<span><select id="stages" name="stages"><option selected="selected"     value="/Regions/250/Tournaments/30/Seasons/3871/Stages/8209">Europa League Group Stages</option>
<option value="/Regions/250/Tournaments/30/Seasons/3871/Stages/7816">Europa League  Qualification</option>
<option value="/Regions/250/Tournaments/30/Seasons/3871/Stages/8158">Europa League Grp. A</option>
<option value="/Regions/250/Tournaments/30/Seasons/3871/Stages/8159">Europa League Grp. B</option>
.
.
<option value="/Regions/250/Tournaments/30/Seasons/3871/Stages/8466">Europa League</option>
</select></span>


</div>


<script type="text/javascript">

$('#breadcrumb-nav select').change(function () {
NG.GA.trackEvent('BreadcrumbNav', this.id);
window.location.href = this.value;
// TODO: Disable all selects?
});

</script>

my code:

Sub ScrapeData()
Dim ie As InternetExplorer
Dim URL As String

URL = "http://www.whoscored.com/Regions/250/Tournaments/30/Seasons/3871/Stages/8466/Fixtures/Europe-UEFA-Europa-League-2013-2014"

Set ie = New InternetExplorer
ie.Visible = True
ie.navigate (URL)

Do
    DoEvents
Loop Until ie.readyState = 4

SelectValue ie, "/Regions/250/Tournaments/30/Seasons/3871/Stages/7816"
SelectValue ie, "/Regions/250/Tournaments/30/Seasons/3871/Stages/8209"

End Sub

Sub SelectValue(ByVal ie As InternetExplorer, ByVal value As String)
Dim htmlDoc As HTMLDocument
Dim ddStages As HTMLSelectElement
Dim idBreadCrumb As Object

Set htmlDoc = ie.document

With ie.document
    Set idBreadCrumb = .getelementbyid("breadcrumb-nav")
    Set ddStages = .getelementbyid("stages")
End With

ddStages.value = value
ddStages.FireEvent ("onchange")
'fireevent on ddStages didn't work so tried here too
idBreadCrumb.FireEvent ("onchange")

Do
    DoEvents
Loop Until ie.readyState = 4

End Sub

Any help would be really appreciated.

Solution

  • There must be some JavaScript executing on the event "the select element has changed its value". My suggestion, much easier than executing the JavaScript, is to just navigate the link (because what the JS does here is just changing the HTML page you are seeing, and not the elements within the same webpage).

    So, for example, I would just replace this:

    SelectValue ie, "/Regions/250/Tournaments/30/Seasons/3871/Stages/7816"

    with this

    ie.Navigate "http://www.whoscored.com/" & "/Regions/250/Tournaments/30/Seasons/3871/Stages/7816"

    to get the exactly same result.