Real projects examples for you to learn Automated Testing_QTP, Selenium, TestPartner, RFT, XML

QTP and TestPartner provide integrated features for you to test multiple data in one test case. In QTP, the feature is called Data Table, and in TestPartner it is called Active Data. However, both of the features have an disadvantage, which is they only support Data Table or Active Data that has a single sheet.

For example, in TestPartner, to make the Active Data working, first, you need to import an excel file to the Active Data asset. If your excel file has multiple data sheets, and you want to use each of them, this becomes impossible, since in the Active Data asset, you can only select one data sheet (done through the option tab). If you really want to use another data sheet, then you have to rename your excel file, and import it again, and this time, select another data sheet in the option tab.

Someone like me, who wants to save space on the Database and make data easy to maintain, would like to use one Excel file that has multiple sheets instead of use multiple Excel files contain only one sheet per file.

How to do this, we should really get ride of using the Active Data from TestPartner or the Data Table from QTP. We can get data directly from an external Excel file (contains single or multiple work sheets). I have written a function for this, and will show it to you below.

In TestPartner, you may want to save the following code in the Shared Module, since this script can be used across different projects:

Function GetExcelSheetData(FilePath As String, SheetName As String)

'On error jump to "Error Handler:" line
On Error GoTo ErrorHandler

'Variable declaration
Dim SheetCount As Integer
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim CellArray() As String 'A dynamic array that is not sized in the Dim statement

'Open the Excel file
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkBook = ObjExcel.Workbooks.Open(FilePath)

'Count how many sheets in the Excel file
SheetCount = ObjWorkBook.WorkSheets.Count

'Store the data from a specific worksheet into an array
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then

        'Count how many rows and columns in the Excel file (only for the used range)
        RowCount = ObjWorkBook.WorkSheets(i).UsedRange.Rows.Count
        ColumnCount = ObjWorkBook.WorkSheets(i).UsedRange.Columns.Count

        'Array is sized with the ReDim statement
        'after RowCount and ColumnCount are given values.
        ReDim CellArray(1 To RowCount, 1 To ColumnCount) As String

        'Store the data from the specific worksheet into CellArray
        For j = 1 To RowCount
            For k = 1 To ColumnCount
               CellArray(j, k) = ObjWorkBook.WorkSheets(i).UsedRange.Cells(j, k)
            Next
        Next

    End If
Next

'Close the Excel file
ObjWorkBook.Close

'Return CellArray
GetExcelSheetData = CellArray()

Exit Function

ErrorHandler: ' This is a normal VBA line label
    MsgBox Err.Description
    ObjWorkBook.Close

End Function

From the above script, you learned the following:
1, How to write a function with input parameters
2, When deal with external files, you better write something to handle errors
3, How to use “On Error GoTo” (more details see post#13)
4, How to define an array dynamically (Dim and Redim)
5, How to use Excel Object Model, including Excel.Application, Excel.Workbook, Excel. Worksheet, and Excel.Range. (see here)
6, How to return a value in a function

Make some modifications for the Function_GetExcelSheetData Function above, you should be able to write another 2 functions: one is called as Function_ExcelSheetRowCount, and the other one is called as Function_ExcelSheetColumnCount. These 2 functions will return how many rows or columns in a specific Excel sheet, and they will be used together with Function_GetExcelSheetData in my next post.

In order to test the above function, you can write a separate test script to execute it (“Function_GetExcelSheetData” is the file name):

Sub Main()
Dim A
A = Function_GetExcelSheetData.GetExcelSheetData("C:\...\MeasurementTab.xlsx", "Sheet1")
End Sub

In TestPartner, if the function does not have a return value, but it has more than 1 input values, then in order to execute the function, you still need to define a variable in your test script, and assign the function to the variable. For example, you have a function “ActiveExcelSheet(FilePath As String, SheetName As String)”, which has no return value, just to active a specific sheet in the Excel file. To execute the function, your script needs to look like this:

Sub Main()
Dim A
A = Function_ActiveExcelSheet.ActiveExcelSheet("C:\...\MeasurementTab.xlsx", "Sheet1")
MsgBox (A) 'this tells you A is nothing
End Sub

If the test script looks like this:

Sub Main()
Function_ActiveExcelSheet.ActiveExcelSheet("C:\...\MeasurementTab.xlsx", "Sheet1")
End Sub

TestPartner will inform you there is a “Syntax error” and won’t allow you to run the script. However, if the function has only one input variable and no return value, then you can execute it without assign it to a variable.

Now, let’s talk about how to take the above function (e.g., the “ActiveExcelSheet” function) to QTP. Since TestPartner uses VBA, but QTP uses VBScript, you need to make the following changes in the function script:
A, The function script needs to be created in the QTP Function Library (File – New – Function Library);
B, In the function script, delete anything related to the ErrorHandler (2 paragraphs);
C, In the function script, delete any definition for variable type, e.g., modify “(FilePath As String, SheetName As String)” to “(FilePath, SheetName)”, and change “Dim SheetCount As Integer” to “Dim SheetCount”.
D, Create a Test/Action to execute the function script, and the test/action will look like this (compare to TestPartner test script, no “Sub” nor “End Sub”, no need to assign the function to a variable, and no function file name nor brackets, i.e., “Function_ActiveExcelSheet.ActiveExcelSheet(…)” changes to “ActiveExcelSheet …”):

ActiveExcelSheet "C:\...\MeasurementTab.xlsx", "Sheet1"

E, Associate the Function file with the Test/Action file (right click on the Function file script and choose “Associate…” on the pop up menu), then you can run the function by running the test/action.

From this post, you may have a better understanding of QTP and TestPartner… Beside the object identifier, all the other features that you need for automation testing actually can be achieved by writing VB scripts in notepad, and save them as .vbs files…

Ok, these are just theoretically speaking, we will still write VB scripts in QTP and TestPartner instead of in notepad, right? Just for the convenience.