Selecting a random row from a data driven test

[Chris]

Hello,
I am developing a set of scripts that creates surveys for sales and marketing. The scripts work great and I love you product. I was wondering if there was a way to randomize the row the script pulls data from an Excel sheet?

I use visual basic for custom coding.
Thank you.
-Chris

[Anthony]

Hi Chris,

Thank you for the excellent feedback.

There is no built-in feature to accomplish this in Test Studio, however it can be done in code. Here's a simple example based on an Excel sheet with a single column and five rows of data. A random number between 1 and 5 is generated, and the text from the corresponding row is entered into the Bing search box.

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

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).get_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

If you want to select a random row with many columns worth of data, consider using our "OnInitializeDataSource" test extension as described here.

All the best,
Anthony
the Telerik team

[Chris]

Thank you Anthony for your obvious hard work.

I received the following error log output running your script from scratch.

I added the reference “Imports Microsoft.Office.Interop”

 

Thank you for your help.

Chris

 

C:\Telerik Projects\Code Testing.tstest.vb: Line 77: (BC30002) Type 'Microsoft.Office.Interop.Excel.Application' is not defined.

C:\Telerik Projects\Code Testing.tstest.vb: Line 78: (BC30002) Type 'Microsoft.Office.Interop.Excel.Workbook' is not defined

.C:\Telerik Projects\Code Testing.tstest.vb: Line 81: (BC30002) Type 'Microsoft.Office.Interop.Excel.Worksheet' is not defined.

C:\Telerik Projects\Code Testing.tstest.vb: Line 83: (BC30002) Type 'Microsoft.Office.Interop.Excel.Range' is not defined.

C:\Telerik Projects\Code Testing.tstest.vb: Line 87: (BC30456) 'Bing' is not a member of 'Pages'.

[Cody]

Hello Chris,

You also need to add an assembly reference to Microsoft.Office.Interop.Excel.dll. If you have Visual Studio installed you can find this file can be found at either:

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office11
D:\Program Files (x86)\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12

If you don't have Visual Studio you will need to download and install The Microsoft Office 2010 Primary Interop Assemblies (PIA) Redistributable. Then add the reference from where that got installed to. This will fix all of the Type 'Microsoft.Office.Interop.Excel.xxxxx' is not defined errors.

The last error: 'Bing' is not a member of 'Pages'. is being caused by this line of code:

Pages.Bing.SbFormQText.Text = value

That was just a sample of how to use the value once you retrieved it. Replace this line of code with wherever you want the value to be inserted or used.

Best wishes,
Cody
the Telerik team