How to Fill a Web Form from Excel using Worksheet Data and 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.

Let us assume, I have few rows of data on my 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.

You may like this post 👉 How to add Hyperlinks dynamically in Excel using VBA

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"

' Now, run the 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 assign value 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

This is important, since, I have used Hyperlinks in the example. You must be wondering (if you are a beginner) how I added those hyperlinks in each row and how the links function.

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.

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

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.

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

👉 Do you know you can write data to a .txt file (or text file) from Excel using VBA? Check out this article.

That's it. Happy coding. 🙂

← PreviousNext →