Store HTML Form to Excel Sheet Using VBScript

In this article you will learn how to Store HTML Form to Excel Sheet using VBScript.

This article explains how to store HTML form data to Microsoft Excel using a VBScript.

Step 1

  1. Start the Excel application.
  2. Create Header label text for each column as in the following diagram.

    Data Column Header
    Figure 1: Data Column Header

  3. Save the Workbook with file extension “xlsx”.

  4. Open Notepad.

  5. Add the following code.
    1. <! DOCTYPE html>  
    2. <Html>  
    3.    <Head>  
    4.       <script language="vbscript" type="text/vbscript">  
    5.          Sub Sample ()  
    6.           Dim iRow  
    7.           Set objExcel = CreateObject ("Excel. Application")  
    8.           Set objWorkbook = objExcel.Workbooks.Open ("C:\Users\Karthikeyan.K\Desktop\Book1.xlsx")  
    9.           objExcel.Application.Visible = True  
    10.           ObjWorkbook.Windows (1).Visible = True  
    11.           Set XlSheet =objWorkbook.Sheets (1)  
    12.           XlSheet.Activate  
    13.           iRow = 1  
    14.           With objExcel  
    15.               Do while .Cells (iRow, 1).value <> ""  
    16.                   .Cells (iRow, 1).activate  
    17.                   iRow = iRow + 1  
    18.               Loop  
    19.                  .Cells (iRow, 1).value=Document.GetElementsByName ("fname") (0).Value  
    20.                  .Cells (iRow, 2).value=Document.GetElementsByName ("lname") (0).Value  
    21.                  .Cells (iRow, 3).value=Document.GetElementsByName ("Add1") (0).Value  
    22.                  .Cells (iRow, 4).value=Document.GetElementsByName ("Add2") (0).Value  
    23.                  MsgBox "Data Added Successfully”, vbinformation  
    24.                  Document.GetElementsByName ("fname") (0).Value=""  
    25.                  Document.GetElementsByName ("lname") (0).Value=""  
    26.                  Document.GetElementsByName ("Add1") (0).Value=""  
    27.                  Document.GetElementsByName ("Add2") (0).Value=""  
    28.              End With  
    29.              ObjWorkbook. Save  
    30.              ObjWorkbook. Close  
    31.              Set objWorkbook = Nothing  
    32.              Set objExcel = Nothing  
    33.          End Sub  
    34.       </script>  
    35.       <style type="text/css">  
    36.          fieldset {  
    37.             border: #00cc00 2px solid;  
    38.             padding: 10px;  
    39.             color: green;  
    40.       </style>  
    41.    <body  
    42. <form>  
    43.    <fieldset>  
    44.       <legend>Csharpcorner</legend>  
    45.       <center>  
    46.          <img src="C:\Users\Karthikeyan.K\Desktop\Add-Male-User.png" alt="Mountain View"><br>  
    47.          First name:<br>   
    48.          <input type="text" name="fname" Value=""><br>  
    49.          Last name :< br>  
    50.          <input type="text" name="lname" Value=""><br>  
    51.          Address1 :< br>  
    52.          <input type="text" name="Add1" Value=""><br>  
    53.          Address2 :< br>  
    54.          <input type="text" name="Add2" Value=""><br>  
    55.          <br>  
    56.          <input type="button" onclick="Sample()" value="Submit" /><br>  
    57.       </center>  
    58.    </fieldset>  
    59. <form>  
    60. </body>  
    61. </html>  
  6. Save the file with the extension “.htm”.

Step 2

Follow the procedure to turn on or turn off VBScript in your Internet Explorer:

  1. Choose Internet Options from the Tools menu.

    Tools
    Figure 2

  2. Select the Security tab from the dialog box.
  3. Click the Custom Level button.

    Security
    Figure 3

  4. Scroll down until you find the Scripting option.

    Scripting option
    Figure 4

  5. Select the Enable radio button under Active scripting.
  6. Finally click OK and get out.

Output

  1. Open the HTML document.

    User Input Form
    Figure 5: User Input Form

  2. Enter the data then press the Submit button.

    User input data
    Figure 6: User input data

    Data added successfully
    Figure 7: Data added successfully

  3. Open the Workbook to check sheet1's data.

    User data sheet
    Figure 8: User data sheet

Conclusion

Thanks for reading! I hope you liked this article. Please provide your valuable suggestions.