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.