Add Excel 2007 Spreadsheet as a Data Source

  • Article

Step1. Create Excel 2007 spreadsheet

1) Create a blank workbook

2) Populate the cells. The first row of the cell block should be the column headers.

3) Highlight the entire cell block including the column headers.

4) On the Formula menu, click on Define Name.

5) Specify the name and the scope.

6)  When you are done, save the workbook.

Step 2. Download and install the 2007 Office Data Connectivity Components.

Step 3. Add the Excel date file as a data source

1) In the Web Test Editor, right-click the top node of the Web test and then click Add Data Source.

2) The New Test Data Source Wizard appears.

3) In the Data source name box type a name for your data source.

4) In the Data source type list click Database.

5) Click Next.

6) Click New Connection.

7) The Choose Data Source dialog box appears. If the Connection Properties dialog box appears, click on the Change button next to the data source text box. The Change Data Source dialog box appears.

8) In the Data source list, select <other>.

9) In the Data provider list, select .NET Framework Data Provider for OLE DB as the provider.

10) (Optional) Clear the Always use this selection check box.

11) Click Continue.

12) The Connection Properties dialog box appears.

13) Select Microsoft Office 12.0 Access Database Engine OLE DB Provider as the provider.

14) Enter the path to the .xlsx file.

15) Click on the Advanced… button.

16) The Anvanced Properties dialog appears.

17) Set the Extended Properties to “Excel 12.0 Xml.”

18) Click OK.

19) Under Choose from the available tables for this data source, select the check box next to each table (named range) that you want to include in the data source.

20) Click Finish