# Random Row from Excel Data Source

### PROBLEM

Is there a way to data drive a step in a test script so that each time the script is run, the test step will select a random row from the Excel data source?

### SOLUTION

In code, generate a random number based on the number of rows. Then use the corresponding text from that row's cell in the applicable test step.

1. Create a basic test against Bing.com.
2.

3. Right click step 2 and select Customize Step in Code.
4. Enter the below code into the coded step. The searchQuery.xlsx Excel file contains five rows of data.
5.

• Note: Ensure you Add an Assembly Reference to Microsoft.Office.Interop.Excel. You can download a version of that file on Microsoft's website that matches your version of MS Office.

`C#`

`Random random = ``new` `Random();`
`int` `num = random.Next(1, 6);`

`string` `input = @``"C:\Data\searchQuery.xlsx"``;`

`Microsoft.Office.Interop.Excel.Application app = ``new` `Microsoft.Office.Interop.Excel.Application();`
`Microsoft.Office.Interop.Excel.Workbook inputBook = app.Workbooks.Open(input, 0, ``false``, 5, ``""``, ``""``, ``false``, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, ``""``, ``true``, ``false``, 0, ``true``, ``false``, ``false``);`
`Microsoft.Office.Interop.Excel.Worksheet inputSheet = (Microsoft.Office.Interop.Excel.Worksheet)((inputBook.Worksheets).get_Item(1));`
`            `
`string` `value = ((Microsoft.Office.Interop.Excel.Range)inputSheet.Cells[num, 1]).Text ``as` `string``;`
`app.Quit();`
`app = ``null``;`

`Pages.Bing.SbFormQText.Text = value;`

`Visual Basic`

`Dim` `random ``As` `New` `Random()`
`Dim` `num ``As` `Integer` `= random.[``Next``](1, 6)`

`Dim` `input ``As` `String` `= ``"C:\Data\searchQuery.xlsx"`

`Dim` `app ``As` `New` `Microsoft.Office.Interop.Excel.Application()`
`Dim` `inputBook ``As` `Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open(input, 0, ``False``, 5, ``""``, ``""``, _`
`    ``False``, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, ``""``, ``True``, ``False``, 0, _`
`    ``True``, ``False``, ``False``)`
`Dim` `inputSheet ``As` `Microsoft.Office.Interop.Excel.Worksheet = ``DirectCast``((inputBook.Worksheets.Item(1)), Microsoft.Office.Interop.Excel.Worksheet)`

`Dim` `value ``As` `String` `= TryCast(``DirectCast``(inputSheet.Cells(num, 1), Microsoft.Office.Interop.Excel.Range).Text, ``String``)`
`app.Quit()`
`app = ``Nothing`

`Pages.Bing.SbFormQText.Text = value`