In the world of software testing and quality assurance, comparing Excel sheets efficiently is crucial, especially when automating repetitive tasks or verifying data accuracy. One of the powerful tools in an automation tester's toolkit is QuickTest Professional (QTP), now known as Micro Focus UFT (Unified Functional Testing). This blog post will guide you through five innovative methods to compare Excel sheets using QTP, ensuring you can automate and streamline this common task.
Method 1: Using QTP’s Built-In Methods
QTP has several built-in methods that can be utilized to compare Excel sheets directly:
- GetCellData: Retrieves the value from a specific cell.
- SetCellData: Sets a value to a specific cell.
You can compare the values from each corresponding cell in two Excel sheets:
Dim ExcelSheet1, ExcelSheet2, iRowCount, jColumnCount
Set ExcelSheet1 = CreateObject("Excel.Application")
ExcelSheet1.Workbooks.Open "C:\Path\To\File1.xlsx"
Set ExcelSheet2 = CreateObject("Excel.Application")
ExcelSheet2.Workbooks.Open "C:\Path\To\File2.xlsx"
iRowCount = ExcelSheet1.Sheets(1).UsedRange.Rows.Count
jColumnCount = ExcelSheet1.Sheets(1).UsedRange.Columns.Count
For i = 1 To iRowCount
For j = 1 To jColumnCount
If ExcelSheet1.Sheets(1).Cells(i, j).Value <> ExcelSheet2.Sheets(1).Cells(i, j).Value Then
Print "Discrepancy found in Row: " & i & " Column: " & j
End If
Next
Next
💡 Note: This method is straightforward for comparing large data sets within Excel sheets but can be slow for very large files.
Method 2: VBScript Custom Functions
For more flexibility, you can use VBScript to create custom comparison functions within QTP:
- Create functions that can handle different comparison criteria.
- Use these functions to compare Excel sheets:
Function CompareSheets(Sheet1Path, Sheet2Path)
Dim Excel1, Excel2, Sheet1, Sheet2, iRowCount, jColumnCount
Set Excel1 = CreateObject("Excel.Application")
Set Excel2 = CreateObject("Excel.Application")
Set Sheet1 = Excel1.Workbooks.Open(Sheet1Path).Sheets(1)
Set Sheet2 = Excel2.Workbooks.Open(Sheet2Path).Sheets(1)
iRowCount = Sheet1.UsedRange.Rows.Count
jColumnCount = Sheet1.UsedRange.Columns.Count
For i = 1 To iRowCount
For j = 1 To jColumnCount
If Not CompareCells(Sheet1.Cells(i, j).Value, Sheet2.Cells(i, j).Value) Then
Print "Discrepancy in Row " & i & ", Column " & j
End If
Next
Next
Excel1.Quit : Excel2.Quit
End Function
Function CompareCells(Value1, Value2)
If IsEmpty(Value1) And IsEmpty(Value2) Then
CompareCells = True
ElseIf Not IsEmpty(Value1) And Not IsEmpty(Value2) Then
CompareCells = (Value1 = Value2)
Else
CompareCells = False
End If
End Function
📝 Note: Custom functions allow for specialized comparisons, such as ignoring certain discrepancies or formatting issues.
Method 3: Database Approach
Import the Excel data into a database for more complex comparisons:
- Connect Excel to a database like SQL Server or Access.
- Write SQL queries to compare the data in both sheets.
Dim conn, rs1, rs2, SQL
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\File1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes"""
SQL = "SELECT * FROM [Sheet1$] WHERE Not EXISTS (SELECT * FROM [C:\Path\To\File2.xlsx].[Sheet1$] WHERE [Sheet1$].[ColumnA] = [Sheet1$].[ColumnA] AND [Sheet1$].[ColumnB] = [Sheet1$].[ColumnB])"
Set rs1 = conn.Execute(SQL)
While Not rs1.EOF
Print "Discrepancy in Row " & rs1("RowID")
rs1.MoveNext
Wend
rs1.Close
conn.Close
🛠 Note: This method is useful for large datasets or when complex comparison logic is needed.
Method 4: Third-Party Tools Integration
Integrate third-party tools like Beyond Compare or WinMerge for visual comparison:
- Automate the tool through QTP to launch and compare files.
- Retrieve and log the comparison results.
Dim objShell
Set objShell = CreateObject("WScript.Shell")
objShell.Run "C:\Program Files\Beyond Compare 4\BCompare.exe ""C:\Path\To\File1.xlsx"" ""C:\Path\To\File2.xlsx"""
' Wait for the comparison to complete
WScript.Sleep 10000 ' Wait for 10 seconds
' Here you could implement ways to capture and log results from Beyond Compare
💻 Note: Third-party tools can provide visual comparisons, which are particularly useful for understanding discrepancies quickly.
Method 5: Scripting with .NET Libraries
Use .NET Excel Interop libraries to automate Excel tasks within QTP:
- Open and manipulate Excel files.
- Compare ranges or entire sheets using .NET features.
Imports Excel = Microsoft.Office.Interop.Excel
Dim exlApp As Excel.Application
Dim wkbk1, wkbk2 As Excel.Workbook
Dim wsht1, wsht2 As Excel.Worksheet
Dim range1, range2 As Excel.Range
Set exlApp = New Excel.Application
exlApp.Visible = True
wkbk1 = exlApp.Workbooks.Open("C:\Path\To\File1.xlsx")
wkbk2 = exlApp.Workbooks.Open("C:\Path\To\File2.xlsx")
Set wsht1 = wkbk1.Sheets(1)
Set wsht2 = wkbk2.Sheets(1)
Set range1 = wsht1.UsedRange
Set range2 = wsht2.UsedRange
For Each Cell1 In range1.Cells
If Cell1.Value <> range2.Cells(Cell1.Row, Cell1.Column).Value Then
Print "Discrepancy in Cell " & Cell1.Address & " : " & range2.Cells(Cell1.Row, Cell1.Column).Address
End If
Next
exlApp.Quit
🚨 Note: This method requires .NET framework, and Excel must be installed on the test system. It's powerful but has more setup overhead.
In wrapping up, we’ve explored a variety of methods for comparing Excel sheets using QTP, each with its own merits depending on the complexity and nature of the data comparison needed:
- Built-in Methods: Simple and efficient for straightforward comparisons.
- Custom Functions: Offer flexibility to define specific comparison logic.
- Database Approach: Ideal for large datasets or complex queries.
- Third-Party Tools: Provide visual comparison capabilities for user-friendly analysis.
- .NET Libraries: Offer advanced Excel manipulation and integration capabilities.
Each method has its place in automation testing workflows, and the choice depends on your project requirements, existing tools, and the level of automation maturity. By leveraging these techniques, you can significantly enhance the reliability and efficiency of your automated tests involving Excel sheets.
Can QTP compare sheets from different versions of Excel?
+
Yes, QTP can work with different versions of Excel as long as the Excel application is installed on the machine where QTP is running. However, compatibility issues might arise, so ensure the Excel versions are compatible.
What if the sheets have different sizes?
+
Methods like built-in functions or custom scripts can handle sheets of different sizes by comparing up to the smaller sheet’s dimensions or by flagging any extra data in the larger sheet.
Can I automate the comparison to run periodically?
+
Yes, with scripting, you can schedule QTP tests to run at specific intervals to compare sheets automatically.
Are these methods compatible with the latest UFT One?
+
Yes, UFT One (formerly known as QTP) supports these methods and might even have enhanced features for Excel manipulation and comparison.
What to do if QTP crashes while comparing large Excel files?
+
If QTP crashes, consider using the database method or splitting the comparison into smaller chunks to handle large files more efficiently.
