How to Fill a Web Form from Excel using Worksheet Data and VBA

In my previous article, I demonstrated how to fill a web form using Excel with hard-coded data. Now, let's take it a step further. In this guide, I'll show you a more dynamic and efficient approach, leveraging worksheet data to automate the process using a simple VBA macro. Say goodbye to manual entries and hello to seamless automation.

👉 The solution still works in 2025.

Fill a web form using Worksheet data in VBA

The Web Form

Here’s a sample contact form, which the macro will fill using the data extracted from an Excel worksheet. The web page has few input box and a button to the save the form data. Usaully, a form is filled manually and after clicking the button, data is saved in a text file. However, the macro (or the code) that I am sharing here, will do the process automatically.

👉 How to write data (like a table data) to a txt file using VBA?

The Macro

Imagine a worksheet populated with multiple rows of data, each consisting of six columns. The first five columns contain essential information required to populate a web form, while the sixth column holds interactive hyperlinks. By clicking these hyperlinks, the corresponding data from adjacent columns is dynamically extracted and used to automate the web form filling process. Check out the image above 👆 for a visual representation of how it works.

I am not using a named range to get the data. It will just loop through each column of the row where the "Hyperlink" is clicked.

Option Explicit
Const sURL = "https://www.encodedna.com/css-tutorials/form/contact-form.htm"

' execute macro by clicking the hyper links.
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    fillWebForm Target.Range.row
End Sub

Private Sub fillWebForm(iRow As Integer)
    ' Define objects.
    Dim oIE As Object
    Dim oHDoc As HTMLDocument
    
    ' Create IE object.
    Set oIE = CreateObject("InternetExplorer.Application")

    With oIE
        .Visible = True       ' Open IE.
        .Navigate sURL        ' Assign the URL to the browser.
    End With

    While oIE.ReadyState <> 4
        DoEvents
    Wend

    Set oHDoc = oIE.Document
    
    ' Get data from each column to fill the web form fields.
    With oHDoc
        .getElementById("txtName").Value = Cells(iRow, 1)
        .getElementById("txtAge").Value = Cells(iRow, 2)
        .getElementById("txtEmail").Value = Cells(iRow, 3)
        .getElementById("selCountry").Value = Cells(iRow, 4)
        .getElementById("msg").Value = Cells(iRow, 5)
        
        .getElementById("bt").Click   ' Click the button on the web form.
    End With
End Sub

The link to the web form is defined in the beginning as a "const sURL = ...". When you click a link (in a particular row), the macro calls a procedure named fillWebForm(). It takes a parameter (the row number).

➡️ I have explained in detail about how the above macro executes in this article

Run the Macro by Clicking the Hyperlink

Since hyperlinks play a key role in the example above, you might be curious about how I added them to each row and how they work. Let’s dive into the process.

I'll explain how.

In Excel VBA, we often use ActiveX button controls to execute tasks efficiently. However, in the example above, I opted for hyperlinks instead of buttons. You might be wondering, how did I insert these links? It's simpler than you think.

Just follow these steps:

1) Set focus on the cell you want to insert a Hyperlink.

Add hyperlink in cell

2) From the top menu, select "Insert" tab and find the option Hyperlink and click it.

Insert hyperlink in Excel

3) In the "Insert Hyperlink" window, choose Place in This Document option. Type the cell reference, for example F2. The "Text to display" option can be any text, for example, Click it.

Hyperlink dialog box in Excel

4) Press Ok.

The macro to trap the Hyperlink click event will be,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$F$2" Then

    End If
End Sub

← PreviousNext →

How to Fill a Web Form from Excel using Worksheet Data and VBA (2025)

The Web Form

Run the Macro by Clicking the Hyperlink