The Web Form
Here’s a sample contact form, which the macro will fill using the data extracted from an Excel worksheet.
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.
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.
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 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
That's it. Thanks for reading. ☺