You can extract contents (data) from any website or a webpage from your Excel worksheet, like extract stock quotes from a web page etc.
Contents in a web page are embed inside HTML elements. That is, the content or the data is written either between HTML tags like <p>, <div> etc. or data is typed in textboxes. Each HTML element on a web page has two important attributes, such as, the id and/or name. The id, in particular makes an HTML element unique. These id’s are often used to extract data from the elements.
Not just extracting, we can pass data to a web page dynamically from Excel, like filling a form.
Here’s a sample form, a contact form that I have designed especially for my Macro to work. The form has few textboxes (or input boxes) and a button, which when clicked will save the form data in a text (or .txt) file.
The macro that I am sharing will fill the form and will automatically click the Save Button (on the sample web page).
Note: I am assuming, you have Internet Explorer (any version like 9, 10, 11 etc.) installed in your computer. There is an IE installed on Windows 10 too.
We need Microsoft’s Internet Explorer to open the web page. Therefore, we’ll first add an object reference. 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 macro.
Option Explicit Const sSiteName = "https://www.encodedna.com/css-tutorials/form/contact-form.htm" Private Sub CommandButton1_Click() Dim oIE As Object Dim oHDoc As HTMLDocument Set oIE = CreateObject("InternetExplorer.Application") ' Open Internet Explorer Browser and keep it visible. With oIE .Visible = True .Navigate sSiteName End With While oIE.ReadyState <> 4 DoEvents Wend Set oHDoc = oIE.Document With oHDoc .getElementById("txtName").Value = "Arun Banik" .getElementById("txtAge").Value = "35" .getElementById("txtEmail").Value = "firstname.lastname@example.org" .getElementById("selCountry").Value = "India" ' Assign value to the dropdown list in the web form. .getElementById("msg").Value = "Hi, I am Arun Banik and this is a test message. :-)" .getElementById("bt").Click End With End Sub
The code executes when you click a button (an ActiveX control). Alternatively, you can simply run the code by press F5 from your VBA IDE. It will open Internet Explorer and will automatically fill the form with data like name, age, email etc and save (download) the data in a .txt file named formData.txt (the name of the .txt file is defined in the web page).
How the Macro executes
In the beginning of the code, I have defined a Const, where I have assigned the URL of the web page.
Const sSiteName = "https://www.encodedna.com/css-tutorials/form/contact-form.htm"
Next, I have created two objects. Object oIE for Internet Explorer and object oHDoc for HTMLDocument property.
Dim oIE As Object Dim oHDoc As HTMLDocument
I have not added any reference of Internet Explorer, rather I have just created an object oIE using CreateObject() method and now I can open IE browser from my worksheet.
Set oIE = CreateObject("InternetExplorer.Application")
The browser is kept visible, so I can see the output (the filling of the form).
With oIE .Visible = True .Navigate sSiteName End With
Theproperty object will give me access to the HTML elements and its attributes on the web page. Now, see this line here …
.getElementById("txtName").Value = "Arun Banik"
The id is txtName. It’s the id of first textbox or input box on the web page, which we are filling. The method is followed by the property Value. That’s how I am assigning values to each textbox.
Note: If the elements have name, then you can use this …
In-addition, there is a dropdown list (with Country names) on the web page (if you have noticed) and using the same method .getElementById(), I’ll assign a value to it.
Finally, the program will click the button on the web page to save the data.
How to get the id of the Elements
Now, at this stage, you might be wondering, how I got the ids from that web page. It is simple. Just follow these steps.
1) Click this link to open the page, preferably on Chrome browser.
2) Set focus on the textbox using the mouse and right click the mouse. Now, choose the option Inspect (if you are using Chrome Browser).
3) It will open DevTools or the Developer Tools window, highlighting the <input> element. You will see id=“txtName”. I am using this id in my Macro.
4) Follow the 1st three steps to get the id’s of other elements.
You can get the ids and names of elements from any website by following the above steps.
Well, that’s it. Let me know if you have any queries regarding Web Scraping.
Thanks for reading. ☺