Facebook

What's New @ QTPSudhakar
*Recorded Session on New Features in UFT 11.5x by Sudhakar Kakunuri. Click Here to Watch.

Cracking the QTP interview - Paperback Book

A new book "Cracking the QTP interview" authored by Sudhakar Kakunuri. This book is a paperback and it was published by TATA McGrawHill. It consists of 400 frequently asked questions with detailed answers for all topics including QTPScripting and VBScripting. Also a framework explained with step by step and you can easily develop by following the steps provided in the book. A list of Framework expected Q&A's are provided... Read More
MRP: Rs.295/-

Buy This Book From
BookAdda
FlipKart BuyBooksIndia

Tuesday, February 7, 2012

Import XLSX Sheet to QTP Datatable

Import XLSX Sheet to QTP Datatable

Lot of people are facing problem with one of the major limitation of QTP. That is importing data from XLSX extension files. But using Excel Object Model we can overcome this problem.
The below function will import the data from XLSX file to QTP 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 lot many places and now they want to enable XLSX support without modifying QTP 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 datatable object methods and properties.
EnableXLSXsupport()
DataTable.ImportSheet "C:\Users\sudhakar\Desktop\Blog 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.
_______________________________________
Please send your Suggestions and Doubts to my yahoo group http://in.groups.yahoo.com/group/qtpsudhakar

6 comments:

  1. But this does not import more than 256 columns. can any on thell me how this can be done.

    ReplyDelete
  2. That is the count of total columns in QTP datatable. in this case you must go with excel object model.

    ReplyDelete
  3. CAN WE USE DATATABLE METHODS instead of excel object model

    ReplyDelete
  4. QTP only support .xls files. That's the I have used excel object model to import xlsx file.

    ReplyDelete
  5. Can we retrieve the .xlsx file with column value more than 256?

    ReplyDelete
  6. I think you are using old MS office, but in New MS office 2010 there is not limitation of column and row in xlxs. You can use n number or row and column, hope it will help you..

    Thanks,
    Manish Soni

    ReplyDelete

All Posts

Show all Object properties without using Object Spy | Deleting Browsing History, Temporary Files, Cookies | How to click on all Google Search Links in all Pages | How to click on all Google Search Links in a Page | VB Script Samples | Working with Recovery Scenarios using Scripting | Virtual Objects in QTP | Diffrent ways to write a statement | Synchronization | Object Identification in QTP | Test Object Model in QTP | Object Repository Types | Managing Object Repositories Using Object Repository Manager | Recording types in QTP | Using Dictionary Objects In QTP | Working with Webtable Object | Descriptive Programming in QTP | Regular Expression for Date and time | Get Runtime Object Properties for a web Object | Get Test Object Properties | Using RunTime Object Properties in QTP | Component, Distributed Component, Automation and Document | QTP Scripting Sample Questions | Advanced VB Script Questions | QTP Version Information Card | WSH Scripting Samples | Working with links Using DOM | File System Object | ListBox Object | Menu Object
Automation Object Model | QTP Tips | How to take screenshot using VBScript? | QTP Scripts Upgrade Tool | Delete Cookies using QTP | How to export/copy KeyWord View Documentation? | Activating a opened window or Browser | VBScript Basics | How to close a windows process through QTP? | How to return multiple values from a function? | What is the limitation of capture screenshot in QTP? | Compare Two bitmaps using QTP | How to update a particulare line in a text file? | Locking and Unlocking Machine in Script Runtime | How to capture the tooltip of a link? | Add and Remove Repositories to an Action In RunTime | When we have to use Update Run Mode? | Use of INI File in QTP | How to Run the analog recorded code which is copied from another test? | What is the maximum size of an Object Repository? | How many action can we create in a single test? | How to connect to SQL without DSN? | What is the use of INI File in QTP? | How to test Slideslow in a page? | How to write DP for an object having many objects in its heirarchy? | How to Convert a number to Currency Format? | how to parameterize links in a web page? | How to Accessing Windows API through vbs? | Advanced VB Script Samples | Executing QTP Scripts on a Remote Computer
Get Objects from Object Repository | Test / Action Iterations | QTP Environment Support Card | Read CSV File Data | Regular Expressions in VBScript | Creating Comments and Action Template | Import CSV File Data in to QTP Datatable | Sample DOM Script On Google Search | Regular Expression in QTP | Sample Script on Descriptive Programming | Object Hierarchies in QTP | Working with Existing Browser Using DOM | Find Hidden Rows in a Table | Preparing and Using Test Data for Automation Test Scenarios | Close latest opened Browser using QTP | Working with Data Table Utility Object | Check whether QTP script is Opened in Read Only Mode? | Quality Center Practice Assignments | All about Validation of Editbox – Part-1 | The Concept of Keyword Driven Framework | How to find Data Type of a Variable? | How to click on dupllicate links in a WebTable? | Difference between Index and Location | All about Validation of Editbox – Part-2 | Click on Object by Holding CTRL Key | Selecting an Environment to Execute Scripts | Descriptive Programming for Virtual Objects | Set Value in EditBox Using DOM | Data-Driven and Keyword-Driven Test Automation Frameworks | Using one Library Functions / Variables in other Library | Features of Object Repository Window and Object Repository Manager | Common DOM Methods & Properties | VBScript Features | Scripting Page Checkpoint