Post
Ask Question

Store HTML Table Data To Excel Spreadsheet Using VBScript

Step 1

  1. Start the Excel application.

  2. 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.

    Excel Spreadsheet
    Figure 1: Excel Spreadsheet

  3. Save the Workbook with file extension “xlsx”.

  4. Open Notepad.

  5. 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">  
    19.   
    20.   
    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  
    40.           
    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>  
    60.   
    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>  
    81.   
    82.                   <input type="button" onclick="Export ()" value="Export" /> <br>  
    83.             </Center>  
    84.          </fieldset>  
    85.       </form>  
    86.    </body>  
    87. </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.

    Internet Options
    Figure 2
  2. Select security tab from the dialog box.

  3. Click the Custom Level button.

    Custom Level button
    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 data table
    Figure 5: User data table

  2. Press the Export button.

    Export user data
    Figure 6: Export user data

    Data Exported Successfully
    Figure 7: Data Exported 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.


Similar Articles