Web Scrapping – How to fill a Web Form from Excel using VBA

← PrevNext →

Web scraping is a process where an application extracts data or content from a web page. You can do this from your Excel worksheet using VBA. I have shared an article here before where I have explained how to extract data from HTML elements using a Macro. In this post, I’ll show you how to fill a web form from your Excel worksheet using VBA.

Fill a web form from Excel using VBA - Web Scraping

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

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 = "arun@hotmail.com"
        .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

The HTMLDocument property 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 .getElementById() method (its an HTML DOM element and JavaScript developers are familiar with it), is commonly used to get an HTML element by its id. The method takes a parameter in the form of an id. Look at this again.

.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 …

oHDoc.getElementsByName

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.

.getElementById("bt").Click

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).

Chrome browser Inspect option

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.

Chrome Developer tools Window

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.

← PreviousNext →