5 Ways to Compare Excel Sheets with UFT

Comparing Excel sheets can be a tedious task, especially when dealing with large datasets or intricate data structures. However, with tools like Unified Functional Testing (UFT), this comparison can be streamlined and automated, making the process much more efficient. In this post, we'll explore five robust methods to compare Excel sheets using UFT, enhancing your data validation and management capabilities.

Using Object-Based Approach

UFT provides an excellent object-based framework that allows users to interact with Excel files as if they were objects in a coding environment. Here’s how you can utilize this approach:

• Create Excel Object: Begin by creating an Excel object which will give you access to manipulate Excel.
• Open Workbooks: Load the Excel files that you wish to compare.
• Traverse Sheets: Loop through the sheets within the workbooks to check for any discrepancies in sheet names, cell values, or formats.

Here’s a snippet of the code to get you started:

Dim appExcel
Set appExcel = CreateObject(“Excel.Application”)
appExcel.Visible = True
Set wb1 = appExcel.Workbooks.Open(“Path\To\Workbook1.xlsx”)
Set wb2 = appExcel.Workbooks.Open(“Path\To\Workbook2.xlsx”)

Direct Comparison of Data

This method focuses on directly comparing cell values from two Excel sheets:

• Loop Through Cells: Compare each cell from Sheet 1 to the corresponding cell in Sheet 2.
• Handle Differences: Log or highlight any differences found, possibly using UFT’s built-in reporting capabilities.

This approach might be less efficient for large files but provides a straightforward way to verify content:

For i = 1 To wb1.Sheets(1).UsedRange.Rows.Count
    For j = 1 To wb1.Sheets(1).UsedRange.Columns.Count
        If Not wb1.Sheets(1).Cells(i, j).Value = wb2.Sheets(1).Cells(i, j).Value Then
            ‘ Handle the difference here, e.g., log it or mark the cell
        End If
    Next j
Next i

🚧 Note: This method can be time-consuming for large datasets; consider optimizing with conditional loops for partial comparisons.

Using UFT’s Excel Datatable

The Data Table in UFT is another powerful tool for Excel sheet comparison:

• Import Excel Data: Import data from both Excel sheets into UFT’s Data Table.
• Compare Using Built-in Functions: Use UFT’s functions like DataTable.CompareSheets to compare rows or columns.

DataTable.ImportSheet “Path\To\Workbook1.xlsx”, 1, DataTable.Globalsheet
DataTable.ImportSheet “Path\To\Workbook2.xlsx”, 1, “Sheet2”
DataTable.CompareSheets DataTable.Globalsheet, “Sheet2”

Comparative Analysis with Custom Functions

For more tailored comparisons, you might:

• Create Custom Functions: Write functions to compare specific aspects like formats, comments, or custom properties.
• Utilize UFT’s VBScript Functions: Leverage VBScript’s string manipulation capabilities for detailed comparison.

Here’s how to compare cell formats:

Function CompareFormats(cell1, cell2)
    If cell1.NumberFormat <> cell2.NumberFormat Then
        CompareFormats = False
    Else
        CompareFormats = True
    End If
End Function

Using Add-Ins for Advanced Comparisons

UFT allows integration with add-ins, which can extend its functionality:

• Third-Party Add-Ins: Integrate add-ins that provide advanced comparison tools.
• Automation Tools Integration: Use tools like Beyond Compare or WinMerge within UFT scripts.

Here’s how you might integrate a comparison tool:

Set objShell = CreateObject(“WScript.Shell”)
objShell.Run “Path\To\ComparisonTool.exe Path\To\Workbook1.xlsx Path\To\Workbook2.xlsx”

When comparing Excel sheets with UFT, each method has its strengths. The object-based approach offers direct manipulation, direct data comparison is straightforward but can be slow, the Data Table method simplifies the process, custom functions allow for detailed control, and integration with add-ins extends capabilities. Remember to choose the method that best fits your needs, considering factors like data size, comparison depth, and the level of automation required. These techniques not only make data validation easier but also increase efficiency and accuracy in managing and comparing large sets of data.

What is UFT?

+

UFT, or Unified Functional Testing, is an automated testing tool developed by Micro Focus. It’s used for functional and regression testing of software applications by automating test processes.

Can I compare Excel sheets from different versions of Excel with UFT?

+

Yes, UFT can handle files from different versions of Excel, as long as the COM object for Excel is compatible with the versions of Excel installed on the system running the tests.

How do I handle errors or mismatches in my Excel comparison script?

+

You can log errors or mismatches in several ways: by writing to an Excel file, using UFT’s reporting features, or integrating with external tools for detailed error reports.