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

akash09

New Member
Joined
Mar 25, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.


1616688810611.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
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:
VBA Code:
    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"
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
HTMLdoc is your ht variable. To be complete, you need this above those lines:
VBA Code:
    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

New Member
Joined
Mar 25, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,761
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:
VBA Code:
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:
VBA Code:
DoEvents
ht.getelementbyid("id").fireevent "onchange"
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top