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

← PrevNext →

In my previous article on Excel automation using VBA, I have explained how to fill a web form and save data from Excel using VBA. The data that I have used in that example were hardcoded. Now, in this post, I’ll show you a more dynamic process by using worksheet data to fill a web form using a simple Macro.

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 Macro

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
Add hyperlinks in Excel using VBA

I am not using a named range or so to extract data. I’ll 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 constant. 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.

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

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

That's it. Thanks for reading.

← PreviousNext →