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

In Post#9, we talked about how to import data from Excel (with multiple sheets) to an Array.

In this post, we will discuss that how to export data from a Two-Dimensional Array (2D array) to an Excel sheet.

Function SaveArrayAsExcelSheet(ArrayData() As String, ArrayRowCount As Integer, ArrayColumnCount As Integer, ExcelFilePath As String, SheetName As String)

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

'Variable declaration
Dim SheetCount As Integer

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

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

'If the sheet named "SheetName" exists, delete it
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        ObjExcel.DisplayAlerts = False  'Disable the delete confirmation message
        ObjWorkBook.WorkSheets(i).Delete
        ObjExcel.DisplayAlerts = True   'Enable the delte confirmation message
        Exit For
    End If
Next

'Create a new sheet in the Excel file and name this sheet as "SheetName"
ObjWorkBook.WorkSheets.Add.Name = SheetName

'Transfer data from an array to a Excel sheet
Dim ObjRange As Excel.Range
For i = 1 To SheetCount
    If ObjWorkBook.WorkSheets(i).Name = SheetName Then
        Set ObjRange = ObjWorkBook.WorkSheets(i).Range(ObjWorkBook.WorkSheets(i).Cells(1, 1), ObjWorkBook.WorkSheets(i).Cells(ArrayRowCount, ArrayColumnCount))
        ObjRange.Value = ArrayData()
    End If
Next

'Save and Close the Excel file
ObjWorkBook.Save
ObjWorkBook.Close

Exit Function

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

End Function

The VBA script above solves the problem (it will add or overwrite a sheet in the Excel file to receive data). The script should be saved as an shared module in TestPartner. To make the script work, you need to go to Tools->Reference to check the reference for Microsoft Excel, otherwise, TestPartner will give you an error on line “Dim ObjRange As Excel.Range”. (see Chapter 8 page 96 of “TestPartner Advanced Training Guide.pdf for Test Partner 5.2” listed in post#8 for more details.)

If you want to apply the script in QTP which uses VBScript instead of VBA, you need to make some minor modifications:

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 “SheetName As String” to “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.,
in Testpartner (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.):

Sub Main()
a = Function_SaveArrayAsExcelSheet.SaveArrayAsExcelSheet(ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")
End Sub

in QTP:

SaveArrayAsExcelSheet ArrayName, 15, 60, "C:\...\MeasurementTab.xlsx", "Sheet2")

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.

The script above should be fast enough to handle 10,000 data, but if your data is more than 50,000, then you may experience some slowness (depends on your computer speed too). There are two ways to solve the speed problem:
1, export the data from array to csv, then convert csv to Excel.
2, use ExcelCreator.NET instead of Excel Object, which is more efficient.
For more details, please refer to this website (you need to understand Chinese) .