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

In my previous article, I have explained how to fill a web form from Excel. The data that I have used in that example were hard-coded. Now, in this article, I’ll show you a more dynamic process by using worksheet data to fill a web form using a simple Macro.

The solution still works in 2024.

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.

The Macro

Let us assume, I have few rows of data in the worksheet with six columns. See the above image. The first five columns have data to fill the web form. The 6th column has Hyperlinks, which when clicked, will extract data from columns.

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 post

Run the Macro by Clicking the Hyperlink

Since I have used Hyperlinks in the above example, you must be wondering how I added those hyperlinks in each row and how the links function.

I'll explain how.

We often use buttons (ActiveX button controls) in our worksheet to perform a task in VBA. In the above example, I have used Hyperlinks instead of buttons. How did I inserted the links? Its simple.
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 (2024)

The Web Form

Run the Macro by Clicking the Hyperlink