VBA Web Scraping Macro - Attempting to select value from a drop down menu on a website and trigger change/update to the website.

[boiled_bread]

Hi,

I am pretty much a newbie when it comes to VBA, but a family member asked me if I could write a script that web scrapes some basic information off of Outback Steakhouse. The link to the specific page is: Outback Steakhouse

My current issue is that when I try to select a state on the drop down menu on their locations directory in VBA, I can't seem to update the page to display the locations. The html code that I am working with is as follows:

<select id="mainContent" tabindex="0" ng-options="obj.state for obj in locationStates track by obj.id" aria-label="Select a state"
ng-model="selectedLocation" ng-change="locations_update()" class="ng-pristine ng-valid ng-empty ng-touched" aria-invalid="false" style="">
<option value="?" selected="selected"></option>
<option label="ALABAMA" value="0">ALABAMA</option>
<option label="ALASKA" value="1">ALASKA</option>
<option label="ARIZONA" value="2">ARIZONA</option>
<option label="ARKANSAS" value="3">ARKANSAS</option>
<!--etc...-->
</select>

Now I am pretty sure that the "ng-change" with "locations_update()" is what I want to trigger (I might be wrong), but I have tried a couple of things and they have not seemed to work.

This is the section of VBA code that I have been using to try to trigger a change so far

Dim SearchBar As Object
Set IE = CreateObject("InternetExplorer.Application")

...

Set SearchBar = IE.document.getElementById("mainContent")
    SearchBar.Focus
    SearchBar.selectedIndex=2
    SearchBar.FireEvent ("onchange")

I realize that there is no "onchange" in the element, but at this point I am clueless on how to approach this. Any help would be appreciated. If anyone needs any more information to help me with this, [;ease let me know. Thanks!

 

[John_w]

Welcome to MrExcel forums.

Using your browser's Developer Tools (F12 key) inspect the select element and see if it has any event handlers. Sometimes they have mouseup, mousedown or click events. The click event can usually be triggered with the normal element.Click method. Other events can be triggered with FireEvent or dispatchEvent.

I also recommend setting a reference to Microsoft HTML Object Library (via Tools -> References in the VBA editor) and then you can declare variables using the proper data types instead of "As Object" (early binding) and see the data type's properties and methods using the editor's intellisense and also see the HTML library classes in the VBA Object Browser (F2 key).

See if this code works for you:

    Dim HTMLdoc As HTMLDocument
    Dim SearchBar As HTMLSelectElement
    #If VBA7 Then
        Dim changeEvent As DOMEvent
    #Else
        Dim changeEvent As Object
    #End If
    
    Set HTMLdoc = IE.document
    Set changeEvent = HTMLdoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    Set SearchBar = HTMLdoc.getElementById("mainContent")
    SearchBar.selectedIndex = 2
    'Either
    SearchBar.FireEvent "onchange"
    'Or
    'SearchBar.dispatchEvent changeEvent

 

[boiled_bread]

Welcome to MrExcel forums.

Using your browser's Developer Tools (F12 key) inspect the select element and see if it has any event handlers. Sometimes they have mouseup, mousedown or click events. The click event can usually be triggered with the normal element.Click method. Other events can be triggered with FireEvent or dispatchEvent.

I also recommend setting a reference to Microsoft HTML Object Library (via Tools -> References in the VBA editor) and then you can declare variables using the proper data types instead of "As Object" (early binding) and see the data type's properties and methods using the editor's intellisense and also see the HTML library classes in the VBA Object Browser (F2 key).

See if this code works for you:

    Dim HTMLdoc As HTMLDocument
    Dim SearchBar As HTMLSelectElement
    #If VBA7 Then
        Dim changeEvent As DOMEvent
    #Else
        Dim changeEvent As Object
    #End If
   
    Set HTMLdoc = IE.document
    Set changeEvent = HTMLdoc.createEvent("HTMLEvents")
    changeEvent.initEvent "change", True, False
    Set SearchBar = HTMLdoc.getElementById("mainContent")
    SearchBar.selectedIndex = 2
    'Either
    SearchBar.FireEvent "onchange"
    'Or
    'SearchBar.dispatchEvent changeEvent

Thank you so much for the help! Sorry for not replying earlier, but your suggestions as well as the SearchBar.dispatchEvent changeEvent solved my issue! I will also make sure to keep note of the other useful information you have provided, because I may have to do this again in the future. Once again, thank you so much!