Random Row from Excel Data Source


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?


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.


  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.

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



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 = 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 = Nothing
Pages.Bing.SbFormQText.Text = value