(11-04-2009, 09:47 AM)Hi Saket, Unfortunately, Comparison of Cell data fails when script jumps to NEXT Sheet of Excel . The Error is " Select method of Range class failed ". Can you correct this code ??? Wrote: here is your modified function
request you to always wrap your code with proper tags, refer helpCode:Function excel_comp
expectedfolder = environment("expfld")
actualfolder = environment("actfld")
difffolder = environment("difffld")
Dim fso, f, fc, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(expectedfolder)
Set fc = f.Files
For Each f1 in fc
expectedfile = expectedfolder + f1.name
actualfile = actualfolder + replace(f1.Name,".xls","a.xls")
DifferenceFile = difffolder + replace(f1.Name,".xls","d.xls")
Set WSShell = CreateObject("WScript.shell")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
ObjExcel.displayAlerts = False
Set objWorkbook2= objExcel.Workbooks.Open(expectedfile)
Set objWorkbook1= objExcel.Workbooks.Open(actualfile)
objWorkbook1.SaveAs DifferenceFile
WScount1=objWorkbook1.Worksheets.Count
WScount2=objWorkbook2.Worksheets.Count
If WScount1<>WScount2 Then
WSShell.Popup "Number of worksheets in file 1 is not equal to Number of worksheets in file 2", 2
Else
For I = 1 To WScount1
Set objWorksheet1= objWorkbook1.Worksheets(I)
Set objWorksheet2= objWorkbook2.Worksheets(I)
For Each cell In objWorksheet1.UsedRange
cell.Select
If cell.value <> "" Then
If instr(1,cell.value,".") Then
If cint(cell.Value) <> cint(objWorksheet2.Range(cell.Address).Value) Then
cell.Interior.ColorIndex = 6 'Highlights in red color if any changes in cells
ObjExcel.displayAlerts = False
objWorkbook1.Save
End If
iPos= instr(1,cell.value,".")+1
If mid(cell.Value,iPos,3) <> mid(objWorksheet2.Range(cell.Address).Value,iPos,3) Then
cell.Interior.ColorIndex = 6 'Highlights in red color if any changes in cells
ObjExcel.displayAlerts = False
objWorkbook1.Save
end if
else
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 6 'Highlights in red color if any changes in cells
ObjExcel.displayAlerts = False
objWorkbook1.Save
Else
cell.Interior.ColorIndex = 0
End If
End If
End If
Next
Next
objExcel.Save
Set objWorksheet1= Nothing
Set objWorksheet2= Nothing
objExcel.Application.Quit
End if
next
Set objExcel=Nothing
end function
Compare Two ex cel sheets and highlight differences
|
|
« Next Oldest | Next Newest »
|
Possibly Related Threads… | |||||
Thread | Author | Replies | Views | Last Post | |
Compare WebTable Elements | saraiado | 1 | 2,637 |
06-11-2015, 06:54 PM Last Post: venkatesh9032 |
|
How to compare two binary values | Naresh | 0 | 2,442 |
09-09-2014, 05:06 PM Last Post: Naresh |
|
Comparing two excel Sheets whose columns names vary | Divya Roopa | 2 | 8,864 |
03-26-2014, 07:20 PM Last Post: Parke |
|
Iteration in Local sheets for UFT11.5 | haithamQTP | 1 | 3,328 |
02-27-2014, 09:12 AM Last Post: supputuri |
|
Record and run differences on "button with context menu" | cem404iuce | 0 | 1,889 |
11-11-2013, 03:07 PM Last Post: cem404iuce |
Users browsing this thread: 4 Guest(s)