How to import data from excel to UFT based on condition

Question

I have an excel sheet which contain data for test cases and each test cases is separated by a column value say script ID and i want to load only row where script ID =1 . How we can achieve this with DataTable.ImportSheet method as this method import all the row having script id=2 and 3

I also try with query string and that is working fine , but i want to achieve this by DataTable.ImportSheet method (project constraints)

Thanks Aman

Answer 1

You can also do it by creating data table first manually.

For example,

Set myxl = createobject("excel.application")
myxl.Application.Visible = true
myxl.Application.DisplayAlerts = false
myxl.Workbooks.Open DataSheetFilePath
set sheet = myxl.ActiveWorkbook.Worksheets("SheetName")
Row=sheet.UsedRange.Rows.Count
Col=sheet.UsedRange.Columns.Count
'Create Data table first
Datatable.AddSheet ("DataTableName")
Set ParameterTable = datatable.GetSheet("DataTableName")
'Add cols from Excel
For j = 1 To Col
    ParameterTable.AddParameter sheet.cells(1,j).value,""
Next

'Begin search in Excel
'Suppose the ID column will be 1
DataTableRow = 1
For i = 1 to Row
    If sheet.cells(i,1).value = 1 Then
        'Add row record to datatable
         ParameterTable.SetCurrentRow (DataTableRow)
         For j = 1 to Col
         ColName = sheet.cells(1,j).value
         Val = sheet.cells(i,j).value
         datatable.Value(ColName,"DataTableName") = Val
         DataTableRow = DataTableRow+1
         Next
    End If
Next