Store HTML Form to Excel Sheet Using VBScript

Karthikeyan K
9y
100.2k
0
3

100

Article

HowtoStoreHtmlFormtoExcelSheetUsingVBScript.zip

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

Step 1

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

Figure 1: Data Column Header
Save the Workbook with file extension “xlsx”.
Open Notepad.
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>
Save the file with the extension “.htm”.

Step 2

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

Choose Internet Options from the Tools menu.

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

Figure 3
Scroll down until you find the Scripting option.

Figure 4
Select the Enable radio button under Active scripting.
Finally click OK and get out.

Output

Open the HTML document.

Figure 5: User Input Form
Enter the data then press the Submit button.

Figure 6: User input data

Figure 7: Data added successfully
Open the Workbook to check sheet1's data.

Figure 8: User data sheet