Store HTML Table Data To Excel Spreadsheet Using VBScript

Karthikeyan K
9y
24.6k
0
0
100
Article

SourceFiles.rar

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