Compare Excel Sheets in UFT: Simple Steps

Comparing Excel Sheets in UFT (Unified Functional Testing, formerly known as QTP - QuickTest Professional) is an essential task for many testers, especially when automated tests involve data-driven scenarios. When dealing with large datasets, manually comparing Excel files can be time-consuming and error-prone. Here, we'll delve into how you can simplify this process using UFT, providing you with a step-by-step guide to ensure your automation scripts are both accurate and efficient.

Understanding UFT and Excel

UFT has powerful integration capabilities with Microsoft Excel, allowing users to read, write, and manipulate Excel data as part of automated tests. This integration is primarily achieved through the Excel Application Object Model and COM (Component Object Model) interface. Here’s how you can leverage these features for comparing Excel sheets:

Step-by-Step Guide to Compare Excel Sheets

Below are the detailed steps to compare Excel sheets within UFT:

1. Preparation:
   • Ensure Microsoft Excel is installed on the machine where UFT is running.
   • Have the Excel files you wish to compare readily accessible.
2. Creating an Excel Object:

   
   Dim excelApp
   Set excelApp = CreateObject("Excel.Application")
           

3. Opening Workbooks:

   
   Dim workbook1, workbook2
   Set workbook1 = excelApp.Workbooks.Open("C:\Path\To\Excel\File1.xlsx")
   Set workbook2 = excelApp.Workbooks.Open("C:\Path\To\Excel\File2.xlsx")
           

4. Selecting Sheets:

   
   Dim sheet1, sheet2
   Set sheet1 = workbook1.Worksheets(1)
   Set sheet2 = workbook2.Worksheets(1)
           

5. Comparing Data:
   • Iterate through rows and columns of both sheets to compare cell values:

   
   Dim i, j
   For i = 1 To sheet1.UsedRange.Rows.Count
       For j = 1 To sheet1.UsedRange.Columns.Count
           If sheet1.Cells(i, j).Value <> sheet2.Cells(i, j).Value Then
               ' Log the difference or perform any action
           End If
       Next j
   Next i
               

6. Logging Differences:
   • Report differences in a log or another Excel sheet for manual review:

   
   If (sheet1.Cells(i, j).Value <> sheet2.Cells(i, j).Value) Then
       Reporter.ReportEvent micFail, "Comparison Failed at Row: " & i & " and Column: " & j, "Value in Sheet1: " & sheet1.Cells(i, j).Value & ", Value in Sheet2: " & sheet2.Cells(i, j).Value
       ' Or write to another Excel for manual review
       workbook1.Sheets("Sheet3").Cells(i, j).Value = "Value Mismatch"
   End If
               

7. Close Excel Objects:

   
   workbook1.Close
   workbook2.Close
   excelApp.Quit
   Set workbook1 = Nothing
   Set workbook2 = Nothing
   Set excelApp = Nothing
           

📝 Note: Always ensure you properly close all objects to avoid memory leaks. Also, check for Excel file compatibility if different versions are used.

Benefits of Using UFT for Excel Comparison

• Automation: UFT reduces the need for manual comparisons, saving time and reducing human error.
• Accuracy: Scripting ensures every cell is compared precisely without oversight.
• Scalability: Comparing large datasets is manageable, allowing for scalability in testing scenarios.

Integrating UFT into your testing workflow not only streamlines the comparison process but also enhances the overall efficiency of data management in automated tests.

Can UFT compare Excel sheets with different structures?

+

UFT can compare Excel sheets even if they have different structures by specifying which columns or rows to compare. However, additional programming would be needed to handle structural differences effectively.

What happens if there are formula-based differences?

+

UFT can report formula-based differences, but it does not compute or evaluate these formulas. It compares the displayed values or the formulas themselves as text strings.

How can I handle comparing Excel files with large datasets?

+

For large datasets, ensure that your script can handle Excel’s performance limitations. It might be useful to compare data in chunks or use database comparisons for efficiency.

Is it possible to automate the correction of differences?

+

Yes, UFT scripts can be programmed to correct differences based on predefined rules or user input. However, care must be taken to ensure this automation does not introduce errors or alter data inadvertently.