Re: How to add or delete columns to a QTP datatable sheet

[Admin]

''*****************************************************************************************************
''Lets 1st add a sheet ex: "flight info"
datatable.AddSheet ("flight info")

''To add a single column to QTP DataTable sheet, for example: to add "FlightNo" column, with "AIR1001" value, to "flight info" sheet.
datatable.GetSheet(SheetName/SheetID).AddParameter ColumnName, ColumnValue ''AddParameter means AddColumn

''Follow the below examples:
datatable.GetSheet("flight info").AddParameter "FlightNo","AIR1001"

''If only want to add the column, the value is left empty:
datatable.GetSheet("flight info").AddParameter "FlightNo",""

''To add multiple columns to QTP DataTable sheet, for example: to add "FlightNo", "DepartureTime" ... etc columns, with no values, to "flight info" sheet:
MySheetName = "flight info"
datatable.GetSheet(MySheetName).AddParameter "FlightNo",""
datatable.GetSheet(MySheetName).AddParameter "DepartureTime",""
datatable.GetSheet(MySheetName).AddParameter "ArrivalTime",""
datatable.GetSheet(MySheetName).AddParameter "Airline",""
datatable.GetSheet(MySheetName).AddParameter "Price",""
datatable.GetSheet(MySheetName).AddParameter "Total",""
datatable.GetSheet(MySheetName).AddParameter "OrderNo",""

''To capture the runtime values:
datatable("FlightNo","flight info") = window("Flight Reservation").WinEdit("Flight No:").GetROProperty("text")
datatable("DepartureTime","flight info") = window("Flight Reservation").WinEdit("Departure Time:").GetROProperty("text")
datatable("ArrivalTime","flight info") = window("Flight Reservation").WinEdit("Arrival Time:").GetROProperty("text")
datatable("Airline","flight info") = window("Flight Reservation").WinEdit("Airline:").GetROProperty("text")
datatable("Price","flight info") = window("Flight Reservation").WinEdit("Price:").GetROProperty("text")
datatable("Total","flight info") = window("Flight Reservation").WinEdit("Total:").GetROProperty("text")
datatable("OrderNo","flight info") = window("Flight Reservation").WinEdit("Order No:").GetROProperty("text")

''Export the sheet/ excel file at the end of your code, as these all columns, values will vanish once the test run is completed:
datatable.ExportSheet "C:\Users\Gajre\Desktop\qtp stuff\flight details.xls", "flight info"
''*****************************************************************************************************

''The other way of adding single on multiple columns is to write a generic function/ sub, with parameters as columns and sheetname:
''see the below example: The below function takes 2 params, one column/ columns, and other sheetName.
'' If multiple columns are to be added, call the below Function, passing the column names seperated by a delimiter,
'' "," (comma) in this case and the desired sheetName.

''To add multiple columns to "flight info" sheet:
ColNames = "FlightNo,DepartureTime,ArrivalTime,Airline,Price,Total,OrderNo"
Call DTAddParams(ColNames,"flight info")

'' Or you may directly mention the column names as parameter, rather using a variable like:
Call DTAddParams("FlightNo,DepartureTime,ArrivalTime,Airline,Price,Total,OrderNo" , "flight info")

''To add only one column to "flight info" sheet:
ColNames = "FlightNo"
Call DTAddParams(ColNames,"flight info")

''This a generic function to add one/ more columns to a desired sheet:
Function DTAddParams(ColNames,MySheetName)
If instr(ColNames,",") > 0 Then ''You may use any delimiter instead of "," (comma).You may pass the delimiter parameter as well.
abc = split(ColNames, ",")
else
abc(0) = ColNames
End If
For i=0 to ubound(abc)
datatable.GetSheet(MySheetName).AddParameter abc(i),""
Next
End Function

''Lastly to delete a column/ columns:
datatable.GetSheet(MySheetName).DeleteParameter ColumnName

''Example: To delete "FlightNo" cloumn from "flight info" sheet.
datatable.GetSheet("flight info").DeleteParameter "FlightNo"

''You may choose to write a generic function for Delete columns, similar to DTAddParams(ColNames,MySheetName) as seen above.

''And to delete a sheet:
datatable.DeleteSheet(MySheetName)

''Hope this is of some help to you.
''Thanks,
''Witworks...

[amitkummy]

I have to add some columns, capture some data into them and delete the other un neccessary columns and sheets.

Can some one guide me through this. As I have a limited knowleadge on QTP and I am seeking help on the above scenario.

Thanks,
Amit