Store HTML Table Data To Excel Spreadsheet Using VBScript

Karthikeyan K

11y58.4k320100Article

HTML ExcelVB Script

32 Comments

Join the conversation! Your thoughts help the community grow.

Lars Schmidt8y ago
Hi Karthikeyan, thank you for this code. it works perfectly. I would like to ask how to extract only form entry into an existing excel sheet as an additional line underneath existing entries there. Thanks so much for your help!!! Best regards Lars
Karthikeyan K10y ago
Thanks for reading
Karthikeyan K10y ago
Hi Vijay VBScript only run in Internet Explorer..
Vijay Bhaskar10y ago
Karthik.. thank you.. you have written wonderful article.. does it work on internet explorer only or on any browser also.. Please can you tell me..
Gowtham Rajamanickam10y ago
Good article
Karthikeyan K11y ago
Thank you Vipul sir
Vipul Malhotra11y ago
nice
- Karthikeyan K11y ago
  Thanks for reading sir
Karthikeyan K11y ago
Thank you Sandeep sir
Karthikeyan K11y ago
Thank you Sibeesh sir
Sandeep Kumar11y ago
Nice Article sir :)
- Karthikeyan K11y ago
  Thanks for reading sir
Sibeesh Venu11y ago
Nice Share
- Karthikeyan K11y ago
  Thanks for reading sir
Karthikeyan K11y ago
Thank you Gowtham sir
Gowtham K11y ago
Good One
- Karthikeyan K11y ago
  Thanks for reading sir
Karthikeyan K11y ago
Thank you Rajeesh sir
Karthikeyan K11y ago
Thank you Vaikesh sir
Karthikeyan K11y ago
Thank you Sumit sir
Sumit Joshi11y ago
Nice Share.
- Karthikeyan K11y ago
  Thanks for reading sir
Vaikesh K P11y ago
Good one
- Karthikeyan K11y ago
  Thanks for reading sir
Karthikeyan K11y ago
Thank you Nilesh sir
Rajeesh Menoth11y ago
Good Karthikeyan K
- Karthikeyan K11y ago
  Thanks for reading sir
Nilesh Jadav11y ago
Nice one Karthikeyan K.
- Karthikeyan K11y ago
  Thanks for reading sir
Karthikeyan K11y ago
Thank you Santhakumar sir...
Santhakumar Munuswamy11y ago
Good one
- Karthikeyan K11y ago
  Thanks for reading sir

Step 1

Start the Excel application.
On the Home tab choose the Cells style to change the header background and sheet background style so it looks as in the following diagram.

Figure 1: Excel Spreadsheet
Save the Workbook with file extension “xlsx”.
Open Notepad.
Add the code shown below.
1. <! DOCTYPE html>
2. <html>
3. <head>
4. <style>
5. table, td, th {
6. border: 1px solid green;
7. }
8. th {
9. background-color: green;
10. color: white;
11. }
12. fieldset {
13. border: #00cc00 2px solid;
14. padding: 10px;
15. Color: green;
16. }
17. </style>
18. <script language="vbscript" type="text/vbscript">
21. Sub Export()
22. Dim mytable
23. Dim mytable1
24. Dim tab
25. Dim n
26. Dim j
27. Set objExcel = CreateObject("Excel.Application")
28. Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Karthikeyan.K\Desktop\Book1.xlsx")
29. objExcel.Application.Visible =True
30. objWorkbook.Windows(1).Visible =True
31. set XlSheet =objWorkbook.Sheets(1)
32. XlSheet.Activate
33. Set tab=document.getElementsByTagName("table")(0)
34. mytable = document.getElementsByTagName("table")(0).rows.length
35. mytable1= document.getElementsByTagName("table")(0).rows(0).cells.length
36. For n = 0 to (mytable-1)
37. For j = 0 To (mytable1-1)
38. XlSheet.Cells (n + 1, j + 1).Value = tab.Rows(n).Cells(j).innertext
39. Next
41. Next
42. MsgBox "Data Exported Successfully",vbInformation
43. objWorkbook. Save
44. objWorkbook. Close
45. Set objWorkbook = Nothing
46. Set objExcel = Nothing
47. End Sub
48. </script>
49. </head>
50. <body>
51. <form>
52. <fieldset>
53. <Center>
54. <img src="C:\Users\Karthikeyan.K\Desktop\excel_file-128.png" alt="face" height="128" width="128">
55. <br>
56. <br>
57. <table>
58. <tr>
59. <th>Firstname</th>
61. <th>Lastname</th>
62. </tr>
63. <tr>
64. <td>Karthik</td>
65. <td>K</td>
66. </tr>
67. <tr>
68. <td>Mithu</td>
69. <td>M</td>
70. </tr>
71. <tr>
72. <td>Vinith</td>
73. <td>R</td>
74. </tr>
75. <tr>
76. <td>Menon</td>
77. <td>A</td>
78. </tr>
79. </table>
80. <br>
82. <input type="button" onclick="Export ()" value="Export" /> <br>
83. </Center>
84. </fieldset>
85. </form>
86. </body>
87. </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 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 data table
Press the Export button.

Figure 6: Export user data

Figure 7: Data Exported Successfully
Open the Workbook to check sheet1's data.

Figure 8: User data sheet

Conclusion

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