I have previously shared a similar post here that explained how you could extract or read data from an HTML element or a web page from your Excel worksheet.
The HTML Page with <select> Dropdown list
Here’s the web page (an HTML page), which has a <select> dropdown list element with some values in it. Open the web page, set focus on the dropdown list, right-click the mouse and choose the inspect option and you can see the values in it. See the image.
Also, see the id of the <select> element. We need this id of the element in our VBA code.
Add a ComboBox in your Excel Worksheet
Open an Excel file and save the file in .xlsm format (macro format). Add a Combobox and button control on your Excel sheet. Both are ActiveX controls. The buttons click event will call a procedure to extract data from the dropdown list and populate the ComboBox. You can write your code on any event.
Add Microsoft HTML Object Library
We’ll first have to add a library reference to our application called Microsoft HTML Object Library. Using the library properties, we can open the web page or an HTML page on the Internet Explorer browser. In-addition, we can few HTML objects that will help extract the values from an element.
To do this, we’ll need to add a library called Microsoft HTML Object Library reference to your application.
From the top menu of your VBA editor, click Tools -> References…. In the References window, find and select Microsoft HTML Object Library and click OK.
Here’s the VBA code.
Option Explicit Const sWebPage = "https://www.encodedna.com/jquery/check-if-select-option-or-dropdownlist-is-selected-using-jquery.htm" Private Sub CommandButton1_Click() fillComboBoxWithSelectDropDown End Sub Private Sub fillComboBoxWithSelectDropDown() ' Create Internet Explorer object. Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False ' Keep IE hidden. IE.Navigate sWebPage ' Wait till IE is fully loaded. While IE.ReadyState <> 4 DoEvents Wend Dim oHDoc As HTMLDocument ' Create document object. Set oHDoc = IE.Document Dim oHEle As HTMLSelectElement ' Create HTML element object. Set oHEle = oHDoc.getElementById("selBooks") ' Get the SELECT element's reference using its ID. Dim iCnt As Integer With oHEle If .Length > 0 Then ' Check the SELECT dropdown has any value (options). cmbBooks.Clear ' Clear the combox box. For iCnt = 1 To .Length - 1 cmbBooks.AddItem .Item(iCnt).Value Next iCnt End If End With ' Clean up. IE.Quit Set IE = Nothing Set oHEle = Nothing Set oHDoc = Nothing End Sub
The constant in the beginning of the code, has the URL of the page where I have the <select> element. You can change this value with another URL.
Const sWebPage = https://www.encodedna.com/jquery/check-if-select-option-or-dropdownlist-is-selected-using-jquery.htm
After creating the Internet Explorer object, I have set its Visible property as False.
IE.Visible = False ' Keep IE hidden.
It will remain hidden and we’ll quietly extract the element’s value. Once the operation is over, we’ll close the browser and clear all its instances.
To extract values from my <select> element, I have created an object of property HTMLSelectElement and assigned the id of the <select> element to the object.
Dim oHEle As HTMLSelectElement ' Create HTML element object. Set oHEle = oHDoc.getElementById("selBooks") ' Get the element reference using its ID.
Finally, we’ll loop through each value in the dropdown list and fill our ComboBox with the values.
Do you know you can easily create a Cascaded ComboBox in Excel? Read this post.
Well, that’s it. Thank for reading.