Visual Basic .NET  

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.

Membership not found