Making Selections in dropdown which alter other options for website scraping using VBA

[akash09]

I am trying to scrape data off a website on the company intranet, on the query page I make selections then click on search to retrieve data, which is then copied to excel.
Some selections made, have to alter other drop-down lists, which is not happening in the approach I have taken.

I can only make selections based on the information that loads in each dropdown when the website loads.

For instance, this a code for element eqp_type, now selecting index 1 in this should alter other dropdown lists. But it doesn't happen, the page doesn't update as it does when I manually click on element under index 1.

Dim eqp_type As HTMLFormElement
Set eqp_type = ht.getElementById("eqp_type")
eqp_type.selectedIndex = 1

Also, attached is the source code for this section.

I am not sure if it's the correct technical term, but the dropdown lists, etc on the selection page are dynamic and alter when I select an option in one list, but with my doesn't happen.


Making selction in any of these criterias should alter lists in other drop downs, for instance selecting index 1 in above code should select REEF and update the condition type list but that does not happen.

 

[John_w]

You probably need to fire the change event and maybe also the click event on the Select element, using either fireEvent or dispatchEvent. Something like this:

    Dim selectElem As HTMLSelectElement
    
    #If VBA7 Then
        Dim clickEvent As DOMMouseEvent
        Dim changeEvent As DOMMouseEvent
    #Else
        Dim clickEvent As Object
        Dim changeEvent As Object
    #End If   
    
    'Ensure page is fully loaded before executing these lines
    
    Set clickEvent = HTMLdoc.createEvent("MouseEvent")
    clickEvent.initEvent "click", True, False
    Set changeEvent = HTMLdoc.createEvent("HTMLEvent")
    changeEvent.initEvent "change", True, False
    
    Set selectElem = HTMLdoc.getElementById("eqp_type")
    selectElem.selectedIndex = 1
    
    'Either:
    selectElem.dispatchEvent clickEvent
    selectElem.dispatchEvent changeEvent
    
    'Or:
    selectElem.FireEvent "onclick"
    selectElem.FireEvent "onchange"

 

[akash09]

Thank you John for the code,
I get an automation error when the code reaches the following line.

clickEvent.initEvent "click", True, False

 

[John_w]

HTMLdoc is your ht variable. To be complete, you need this above those lines:

    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument

    Set IE = New InternetExplorer
    With IE
        .navigate "http://url"
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .document
    End With

and references to MS Internet Controls and HTML Object Library.

 

[akash09]

Yes, I used ht in place of htmldoc and have the references set up, still had this error. In the event listeners, the listeners have different javascripts.

I am able to trigger a fire event with
ht.getelementbyid("id").value= "AU" 'this is making the selection
ht.getelementbyid("id").fireevent "onclick" 'codes runs past without any error but dynamic lists don't update.

 

[akash09]

I have set ht as ie.document

 

[John_w]

It's difficult to help you without access to the site. A few ideas:

I get an automation error when the code reaches the following line.

Either add the web site to IE's Trusted Sites, or use InternetExplorerMedium instead of InternetExplorer:

Dim IE As InternetExplorerMedium
Set IE = New InternetExplorerMedium

I am able to trigger a fire event with
ht.getelementbyid("id").value= "AU" 'this is making the selection
ht.getelementbyid("id").fireevent "onclick" 'codes runs past without any error but dynamic lists don't update.

Try continuing with:

DoEvents
ht.getelementbyid("id").fireevent "onchange"