Selecting a random row from a data driven test

4 posts, 0 answers
  1. Chris
    Chris avatar
    17 posts
    Member since:
    Sep 2011

    Posted 19 Oct 2011 Link to this post

    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
  2. Anthony
    Admin
    Anthony avatar
    19 posts

    Posted 19 Oct 2011 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. Chris
    Chris avatar
    17 posts
    Member since:
    Sep 2011

    Posted 21 Oct 2011 Link to this post

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

  4. Cody
    Admin
    Cody avatar
    3354 posts

    Posted 22 Oct 2011 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top