UFT/QTP – Working with Data Table & Excel Object Model

Working with Data Table Object

To work with data present in the Data Table UFT supports three objects name Datatable Object, DTParameter Object and DTSheet Object. These objects support various methods and properties to interact with data in the data table.

Data Table Object

Datatable objects refers to the entire run time Datatables in UFT. Datatable has sheets in it and you can perform many operations like adding sheet, deleting sheet and exporting sheet on datatable object.

Method Name Description Syntax
AddSheet Add new Sheet to data table DataTable.AddSheet(SheetName)
DeleteSheet Delete existing sheete from data table DataTable.DeleteSheet SheetID
ExportSheet Export sheet to an external excel file DataTable.ExportSheet(FileName,SheetName)
Export Export entire data table to an external excel file DataTable.Export(FileName)
GetCurrentRow Get current active row of global sheet DataTable.GetCurrentRow
GetRowCount get the number of rows in global sheet DataTable.GetRowCount
GetSheet Returns the specified sheet from the data table. DataTable.GetSheet(SheetID)
GetSheetCount Returns the total number of sheets in the data table. DataTable.GetSheetCount
Import Imports a specific external Excel file to the data table. DataTable.Import(FileName)
ImportSheet Imports the specified sheet of the specific excel file to the destination sheet. DataTable. ImportSheet(FileName, SheetSource, SheetDest)
SetCurrentRow Sets the Focus of the Current row to the Specified Row Number DataTable.SetCurrentRow(RowNumber)
SetNextRow Sets the focus of the next row in the run-time data table DataTable.SetNextRow
SetPrevRow Sets the focus of the previous row in the run-time data Table DataTable.SetPrevRow
Data Table Object Properties
Property Name Description Syntax
GlobalSheet Returns the global sheet object. DataTable.GlobalSheet
LocalSheet Returns the specified local sheet object. DataTable.LocalSheet
RawValue Retrieves the raw value of the cell DataTable.RawValue ParameterID, [SheetID]
Value Retrieves the value of the cell in the specified parameter. DataTable.Value(ParameterID, [SheetID])

DTSheet Object

This is a sheet in the run time Data Table. This has the following methods or properties.

Method Name Description Syntax
AddParameter Adds the specified column to the sheet in the data table. DTSheet.AddParameter(ParameterName, Value)
DeleteParameter Deletes the specified parameter from the data table. DTSheet.DeleteParameter(ParameterID)
GetCurrentRow Returns the row number of the active row in the data table. DTSheet.GetCurrentRow
GetParameter Returns the specified parameter from the data table. DTSheet.GetParameter(ParameterID)
GetParameterCount Returns the total number of columns in the data table. DTSheet.GetParameterCount
GetRowCount Returns the number of rows in the data table. DTSheet.GetRowCount
SetCurrentRow Makes a particular row as active. DTSheet.SetCurrentRow(RowNumber)
SetNextRow Makes the next row to current row as active. DTSheet.SetNextRow
SetPrevRow Sets the previous row to current row as active. DTSheet.SetPrevRow

DTParameter Object

This represents a parameter (column) of a sheet in the run time data table. DTParameter object has only properties and there are no methods.

Method Name Description Syntax
Name Returns the name of the parameter in the run-time data table. DTParameter.Name
RawValue Returns the raw value of the cell in the current row of the run-time data table. DTParameter.RawValue
Value Retrieves or sets the value of the cell in the Active row of the parameter in the run-time data table. DTParameter.Value
ValueByRow Retrieves the value of the cell in the specified row of the parameter in the run-time data table. DTParameter.ValueByRow(RowNum)

Scripting Example

  1. Write a program to import an excel file to a data table
    Datatable.Import(“D:\UFT\ImportData.xls”)
  2. Write a program to read data from the first parameter of the global sheet.
    Set dsGlobal = Datatable.getSheet(“Global”)
    set colFirst = dsGlobal .GetParameter(1)
    dsRowCount = dsGlobal.GetRowCount
    print(colFirst.Name)
    for I=1 to dsRowCount
    print(colFirst.ValueByRow(I))
    Next
  3. Write a program to read data from all sheets and all parameters of excel file.
    Datatable.Import(“D:\UFT\empdata.xls”)
    shtcount = Datatable.getSheetCount()
    for sheet=1 to shtcount
    set CurrentSheetObj = Datable.GetSheet(sheet)
    ColCount = CurrentSheetObj.GetParameterCount()
    rowCount = CurrentSheetObj.GetRowCount()
    print(CurrentSheetObj.Name)
    for row=1 to rowCount
    rowdata = “”
    for col=1 to ColCount
    set currentColObj = CurrentSheetObj.GetParameter(col)
    rowdata = rowdata & vbtab & currentColObj.ValueByRow(row)
    Next
    print(rowdata)
    Next
    Next

Working with Excel Object

Microsoft has developed the Excel application with heirarachy of object model.We can do excel operations using excel object model. The following are the objects related to microsoft ofiice Excel object model:

QTP Excel

  • Excel.Application (Set objExcel = CreateObject(“Excel.Application”))
  • Excel.Workbook (Set objwb = objExcel.Workbooks.open “path”)
  • Excel.Worksheet (Set objSheet = objwb.Worksheets(“SheetName”))
  • Excel.Range (objSheet.cells(Row,Column))

Some Excel Examples –

Create excel file and enter some data save it

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Add New Workbook
Set workbooks=excel.Workbooks.Add()

‘Set the value in First row first column
excel.Cells(1,1).value=”automationtutorial.com”

‘Save Work Book
workbooks.saveas”C:\exceldemo.xls”

‘Close Work Book
workbooks.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set workbooks=Nothing
Set excel=Nothing

Reading Values from a Specific excel Sheet

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Open the Excel File
Set workbook=excel.Workbooks.Open(“C:\exceldemo.xls”)

‘Get the Control on Specific Sheet
Set worksheet1=excel.Worksheets.Item(“Sheet1”)

‘ Display the Values
Msgbox  worksheet1.cells(1,1).value

‘Close Work Book
workbook.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set worksheet1=Nothing
Set workbook=Nothing
Set excel=Nothing

Deleting Rows from Excel Sheet

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Open the Excel File
Set workbook=excel.Workbooks.Open(“C:\exceldemo.xls”)

‘Get the Control on Specific Sheet
Set worksheet1=excel.Worksheets.Item(“Sheet1”)

‘Delete Row1
worksheet1.Rows(“1:1”).delete

‘Save Excel
workbook.SaveAs(“C:\exceldemo.xls”)

‘Close Work Book
workbook.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set worksheet1=Nothing
Set workbook=Nothing
Set excel=Nothing

Add and Delete ExcelSheet

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Open Existing Excel File
Set workbook=excel.Workbooks.Open(“C:\exceldemo.xls”)

‘Add New Sheet
Set newsheet=workbook.sheets.Add

‘Assign a Name
newsheet.name=”Test”

‘Delete Sheet
Set delsheet=workbook.Sheets(“Test”)
delsheet.delete

‘Close Work Book
workbook.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set newsheet=Nothing
Set delsheet=Nothing
Set workbook=Nothing
Set excel=Nothing

Copy an Excel Sheet of one Excel File to another Excel File

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Open First Excel File
Set workbook1=excel.Workbooks.Open(“C:\exceldemo.xls”)

‘Open Second Excel File
Set workbook2=excel.Workbooks.Open(“C:\exceldemo2.xls”)

‘Copy data from first excel file sheet
workbook1.Worksheets(“Test”).usedrange.copy

‘Paste Data to Second Excel File Sheet
workbook2.Worksheets(“Sheet1”).pastespecial

‘Save Workbooks
workbook1.Save
workbook2.Save

‘Close Workbooks
workbook1.Close
workbook2.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set workbook1=Nothing
Set workbook2=Nothing
Set excel=Nothing

Comapre Two Excel Sheets Cell By Cell for a specific Range

‘Create Excel Object
Set excel=createobject(“excel.application”)

‘Make it Visible
excel.Visible=True

‘Open Excel File
Set workbook=excel.Workbooks.Open(“C:\exceldemo.xls”)

‘Get Control on First Sheet
Set sheet1=excel.Worksheets.Item(“Sheet1”)

‘Get Control on Second Sheet
Set sheet2=excel.Worksheets.Item(“Sheet2”)

‘Give the specific range for Comparision
CompareRangeStartRow=1
NoofRows2Compare=4
CompareRangeStartColumn=1
NoofColumns2Compare=4

‘Loop through Rows
For r=CompareRangeStartRow to(CompareRangeStartRow+(NoofRows2Compare-1))

‘Loop through columns
For c=CompareRangeStartColumn to(CompareRangeStartColumn+(NoofColumns2Compare-1))

‘Get Value from the First Sheet
value1=Trim(sheet1.cells(r,c))
‘Get Value from the Second Sheet
value2=Trim(sheet2.cells(r,c))

‘Compare Values
If value1<>value2 Then

‘ If Values are not matched make the text with Red color
sheet2.cells(r,c).font.color=vbred

End If

Next

Next

‘Save workbook
workbook.Save

‘Close Work Book
workbook.Close

‘Quit from Excel Application
excel.Quit

‘Release Variables
Set sheet1=Nothing
Set sheet2=Nothing
Set workbook=Nothing
Set excel=Nothing

Leave a Reply

Your email address will not be published. Required fields are marked *