How to import all working sheets of an excel file to QTP DataTable: Excel Automation

Hello, friends

This post is meant for QTP learner and who are novice at it. You know that DataTable plays a big role in parameterizing our automation test. We pass arguments to our test script using this dataTable provided by QTP. However, in automation testing of a complex and large scale project we become less dependent on DataTable. Most of our test data is stored in Excel sheet because it gives us a flexibility to modify when needed. We know that to import and excel sheet, we use following commands: 

                               i)DataTable.Import(FilePath)

                               ii) DataTable.ImportSheet "FilePath", "SourceSheet", "DestinationSheet"

If your excel file contains more than one sheet, then none of the above command gives you the ability to import all the sheets at a time. In this case, you have to meet the requirement pragmatically. Sometimes, there is a requirement of using different local sheets at a time during execution. To get ride of such situation or to ease the process, I would like to share the following code snippet with you to achieve that goal. Code is given in Function format for reusability.

   Function ImportAllSheetsToDataTable(FileName)

       Dim objExl, objWBook

       If  Not IsNull(FileName) Then        'Check if the File name has been passed.

    Set objExl=CreateObject("Excel.Application")

    Set objWBook=objExl.Workbooks.Open(FileName,,True) 'Open the excel file

For each oWSheet in objWBook.WorkSheets  'Read all the worksheets

DataTable.AddSheet oWSheet.Name

DataTable.ImportSheet FileName, oWSheet.Name,oWSheet.Name

Next

        Set objWBook = Nothing

    objExl.Quit      'Quit the Application

    Set objExl = Nothing

Else

msgbox "No File name has been passed"

End IF

   End Function

   'To call the Function

   sPath="C:\TestData\TestData_QA.xls"

   ImportAllSheetsToDataTable(sPath)

Hope this code will also help

How to identify a WebButton under a WebTable on a page

We, the automation tester frequently encounter web table on web page. WebTable is a way by which developers use to display information or data to user in tabular form on web page. Handling webtable is not a big deal in automation testing. But handling objects inside the table needs care. You must have faced such situation where you need to identify the button or link to perform "click" operation on it to open the respective page. It is simple if there is only one object of that type.
What will you do if there is more than one object of that type with same sets of property? Probably, you remember how to use index property and that will solve the issue at hand. If you are asked to identify a button and click on it based on some arguments, what you are going to do. Then we will be going into a bit complex thing. First you need to determine which button to identify and then click. To find the button, we need to know the row and column number of the cell of the table.
Let me proceed with an assignment. Assignment is- You need to delete the account with the name "Acc Nick Name". Look at the image below. This is a list containing different account nos. From here you need to search for "Acc Nick Name" and hit the "Delete" button to delete the account. Since you never know how many accounts this list may contain. So the behavior of this list is dynamic and each "Delete" button is of same nature.

Let's get into action. I pull up the spy button and get to see below mentioned hierarchy.

As you can see that the Delete button is located inside the webtable. Now, we are going use the Object Repository to store the object. To achieve that, I started recording the script. While recording I click on any of the Delete button. As a result, QTP automatically saved all the objects it has interacted with. Then I change the Title of the browser to broWCP and Page to pgWCP in the OR. Below is the code.

  Browser("broWCP").Page("pgWCP").WebButton("Delete")

You noticed that web table is missing from the recorded script. This is because QTP conceals the complete hierarchy. Now, remove the WebButton part and type WebTable, it will automatically display the name of the table. So, the code will look like this:

  Browser("broWCP").Page("pgWCP").WebTable("HSA Custodian")

Now, we need to find out how many rows and column the table has. Then we need to locate the Delete button based on the Account NickName. Here is the complete code:

Dim iRow, iCol, sRow, sButton

Dim bStatus:bStatus=False

sAccount="Acc Nick Name" 'Account Name to delete

iRow= Browser("broWCP").Page("pgWCP").WebTable("HSA Custodian").RowCount 'Find out the total row

iCol= Browser("broWCP").Page("pgWCP").WebTable("HSA Custodian").ColumnCount(iRow) 'Find the total Column

For i=1 to iRow 'get the row no where the target item exists

  If Browser("broWCP").Page("pgWCP").WebTable("HSA Custodian").GetCellData(i,3)= sAccount Then

      sRow=i

      bStatus=True

      Exit for

  Else

      bStatus=False

  End If

Next

  If bStatus Then

      Set sButton=Browser("broWCP").Page("pgWCP").WebTable("HSA Custodian").ChildItem(sRow, iCol, "WebButton", sRow-2)

      sButton.Highlight

      sButton.click

  Else

      Msgbox "Could not find the item"

  End IF

We know that the field "Account Nickname" exits on the 3rd column of the table. We have also identified that all the "Delete" buttons located in the last column of the table, hence we used whatever value is assigned to iCol. Later part "sRow -2" is due to the fact that index starts with "0" and our first row of the table contains the column heading. Hence, we need to subtract 2 from sRow. Last argument of childitem method is the index no.

This is how we can handle dynamic button inside a WebTable. Hope you enjoyed learning this fact.