Import XLSX Sheet to UFT/QTP Datatable
Most of the people are facing problem with one of the major limitation of QTP/UFT. That is importing data from XLSX extension files. QTP/UFT does not support directly Import option below QTP/UFT 12 versions But using Excel Object Model we can overcome this problem.UFT 12 and above version can support this XLSX extension files.
The below function will import the data from XLSX file to QTP/UFT specified data sheet.
Function ImportSheetFromXLSX(dFileName,dSourceSheetName,dDestinationSheetName)
Dim ExcelApp
Dim ExcelFile
Dim ExcelSheet
Dim sRowCount
Dim sColumnCount
Dim sRowIndex
Dim sColumnIndex
Dim sColumnValue
Set ExcelApp=CreateObject("Excel.Application")
Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)
Set qSheet=DataTable.GetSheet(dDestinationSheetName)
sColumnCount= ExcelSheet.UsedRange.Columns.Count
sRowCount= ExcelSheet.UsedRange.rows.count
For sColumnIndex=1 to sColumnCount
sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
sColumnValue=Replace(sColumnValue," ","_")
If sColumnValue="" Then
sColumnValue="NoColumn"&sColumnIndex
End If
Set qColumn=qSheet.AddParameter (sColumnValue,"")
For sRowIndex=2 to sRowCount
sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
qColumn.ValueByRow(sRowIndex-1)=sRowValue
Next
Next
Set ImportSheetFromXLSX=qSheet
ExcelFile.Close
ExcelApp.Quit
End Function
There is also another way of overwriting Datatable object to import XLSX files. This is useful for the projects which are already used Datatable.ImportSheet in such many places and now they want to enable XLSX support without modifying QTP/UFT script. Below is the example on how to over write datatable object with newly created class. If you use below code you need to update all existing QTP/UFT datatable object methods and properties.
EnableXLSXsupport()
DataTable.ImportSheet "C:\Users\thirupathi\Desktop\Data\test.xlsx","Sheet2","Action1"
'********************************************************************************************************
Function EnableXLSXsupport()
ExecuteGlobal "Dim QTPDataTable"
Set QTPDataTable=Datatable
ExecuteGlobal "Dim Datatable"
Set Datatable=New CustomDatatable
End Function
'********************************************************************************************************
Class CustomDatatable
Function ImportSheet(dFileName,dSourceSheetName,dDestinationSheetName)
Dim ExcelApp
Dim ExcelFile
Dim ExcelSheet
Dim sRowCount
Dim sColumnCount
Dim sRowIndex
Dim sColumnIndex
Dim sColumnValue
Set ExcelApp=CreateObject("Excel.Application")
Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)
Set qSheet=QTPDataTable.GetSheet(dDestinationSheetName)
sColumnCount= ExcelSheet.UsedRange.Columns.Count
sRowCount= ExcelSheet.UsedRange.rows.count
For sColumnIndex=1 to sColumnCount
sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
sColumnValue=Replace(sColumnValue," ","_")
If sColumnValue="" Then
sColumnValue="NoColumn"&sColumnIndex
End If
Set qColumn=qSheet.AddParameter (sColumnValue,"")
For sRowIndex=2 to sRowCount
sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
qColumn.ValueByRow(sRowIndex-1)=sRowValue
Next
Next
'Set Datatable=QTPDataTable
ExcelFile.Close
ExcelApp.Quit
'Set QTPDataTable=Nothing
End Function
'********************************************************************
Function AddSheet(shtName)
Set AddSheet=QTPDataTable.AddSheet(shtName)
End Function
'********************************************************************
End Class
If you uncomment the Set Datatable=QTPDatatable and Set QTPDatatable =Nothing then this function only works for once. When ever you want to import XLSX sheet then you need to call EnableXLSXsupport() before you use import sheet. This way is for the experts who can handle overwriting of reserved objects. Do remember that If you have not uncommented, the existing datatable methods will not work and you need to define each method in customdatatable class in order to work. You can observe the "AddSheet" method in the above class example
Most of the people are facing problem with one of the major limitation of QTP/UFT. That is importing data from XLSX extension files. QTP/UFT does not support directly Import option below QTP/UFT 12 versions But using Excel Object Model we can overcome this problem.UFT 12 and above version can support this XLSX extension files.
The below function will import the data from XLSX file to QTP/UFT specified data sheet.
Function ImportSheetFromXLSX(dFileName,dSourceSheetName,dDestinationSheetName)
Dim ExcelApp
Dim ExcelFile
Dim ExcelSheet
Dim sRowCount
Dim sColumnCount
Dim sRowIndex
Dim sColumnIndex
Dim sColumnValue
Set ExcelApp=CreateObject("Excel.Application")
Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)
Set qSheet=DataTable.GetSheet(dDestinationSheetName)
sColumnCount= ExcelSheet.UsedRange.Columns.Count
sRowCount= ExcelSheet.UsedRange.rows.count
For sColumnIndex=1 to sColumnCount
sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
sColumnValue=Replace(sColumnValue," ","_")
If sColumnValue="" Then
sColumnValue="NoColumn"&sColumnIndex
End If
Set qColumn=qSheet.AddParameter (sColumnValue,"")
For sRowIndex=2 to sRowCount
sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
qColumn.ValueByRow(sRowIndex-1)=sRowValue
Next
Next
Set ImportSheetFromXLSX=qSheet
ExcelFile.Close
ExcelApp.Quit
End Function
There is also another way of overwriting Datatable object to import XLSX files. This is useful for the projects which are already used Datatable.ImportSheet in such many places and now they want to enable XLSX support without modifying QTP/UFT script. Below is the example on how to over write datatable object with newly created class. If you use below code you need to update all existing QTP/UFT datatable object methods and properties.
EnableXLSXsupport()
DataTable.ImportSheet "C:\Users\thirupathi\Desktop\Data\test.xlsx","Sheet2","Action1"
'********************************************************************************************************
Function EnableXLSXsupport()
ExecuteGlobal "Dim QTPDataTable"
Set QTPDataTable=Datatable
ExecuteGlobal "Dim Datatable"
Set Datatable=New CustomDatatable
End Function
'********************************************************************************************************
Class CustomDatatable
Function ImportSheet(dFileName,dSourceSheetName,dDestinationSheetName)
Dim ExcelApp
Dim ExcelFile
Dim ExcelSheet
Dim sRowCount
Dim sColumnCount
Dim sRowIndex
Dim sColumnIndex
Dim sColumnValue
Set ExcelApp=CreateObject("Excel.Application")
Set ExcelFile=ExcelApp.WorkBooks.Open (dFileName)
Set ExcelSheet = ExcelApp.WorkSheets(dSourceSheetName)
Set qSheet=QTPDataTable.GetSheet(dDestinationSheetName)
sColumnCount= ExcelSheet.UsedRange.Columns.Count
sRowCount= ExcelSheet.UsedRange.rows.count
For sColumnIndex=1 to sColumnCount
sColumnValue=ExcelSheet.Cells(1,sColumnIndex)
sColumnValue=Replace(sColumnValue," ","_")
If sColumnValue="" Then
sColumnValue="NoColumn"&sColumnIndex
End If
Set qColumn=qSheet.AddParameter (sColumnValue,"")
For sRowIndex=2 to sRowCount
sRowValue=ExcelSheet.Cells(sRowIndex,sColumnIndex)
qColumn.ValueByRow(sRowIndex-1)=sRowValue
Next
Next
'Set Datatable=QTPDataTable
ExcelFile.Close
ExcelApp.Quit
'Set QTPDataTable=Nothing
End Function
'********************************************************************
Function AddSheet(shtName)
Set AddSheet=QTPDataTable.AddSheet(shtName)
End Function
'********************************************************************
End Class
If you uncomment the Set Datatable=QTPDatatable and Set QTPDatatable =Nothing then this function only works for once. When ever you want to import XLSX sheet then you need to call EnableXLSXsupport() before you use import sheet. This way is for the experts who can handle overwriting of reserved objects. Do remember that If you have not uncommented, the existing datatable methods will not work and you need to define each method in customdatatable class in order to work. You can observe the "AddSheet" method in the above class example