对比excel中两个sheet中的数据

Option Explicit
w5]Jx~0'**********************************************************
'd M!v,p~-M'?0'目的:   发现Excel中两个Sheet中的数据是否不同并打标记
!Y
iN#{%| MKg L"_0'           绿色 - 匹配51Testing软件测试网yrG${j
}l3k:js/C
'           红色 - 不匹配51Testing软件测试网1z/dB:aH3xq
'         
d7v&r7o3LhY$o }n%X0'参数:    strFilePath51Testing软件测试网V!c[ QU;dk
I
'         strFirstSheetName51Testing软件测试网}
pr3]s x3jpy
'         strSecondSheetName51Testing软件测试网)K?0`
Bf
'**********************************************************51Testing软件测试网8{Q q_0A

Sub Compare2ExcelSheetsInTheSameFile(strFilePath, strFirstSheetName, strSecondSheetName) 51Testing软件测试网/ZP1V]2tq,`
    51Testing软件测试网+`&C`,ku9E
    Dim   objExcelApplication 51Testing软件测试网 N6XoVx ye y#X1m1P
    
ig o"n,xo;UZ#t0    Dim   rngActualSheetData   'Will hold a MS Excel range object
XUZvL2M G+Y'j0    Dim   rngExpectedSheetData51Testing软件测试网YOdJ'[m
       51Testing软件测试网6@AHb,`E6l
    Dim   intRowIndex
"a:ZVd`6C.Na0    Dim   intColumnIndex
jV|/W L%c*d9FN0   
,hWBp,\F`C(D(zX0    Dim   intAreDifferent
9?2uv+yMGl1n0    51Testing软件测试网*?#r_M&h^2aIDj
    intAreDifferent = 1       '1 - there are no diferences between the sheets, 0 - the sheets are different 51Testing软件测试网"Z{ K(Ih ik
   
W0UB:bT0    Set ōbjExcelApplication = CreateObject("Excel.Application")51Testing软件测试网(G]fU4OE'|v
    objExcelApplication.Workbooks.open (strFilePath)
D"z6{Hl)j5s0    
M
AE2Y%MeRx0    Set rngActualSheetData = objExcelApplication.Worksheets(strFirstSheetName).UsedRange51Testing软件测试网h%jF"C;ci$h#@
    Set rngExpectedSheetData = objExcelApplication.Worksheets(strSecondSheetName).UsedRange51Testing软件测试网+\x X'_5z$wK.HK7?
 51Testing软件测试网c'?0vt3qA9R
    'Initiallization of the sheets so that it will be possible to understand what went wrong 51Testing软件测试网#Q
xv!e n@[:d xU1v"V
    objExcelApplication.Worksheets(strFirstSheetName).UsedRange.Font.Color = vbBlack
a7@$Q,P;LYyY
P3J%\8Mw0    objExcelApplication.Worksheets(strSecondSheetName).UsedRange.Font.Color = vbBlack
cB6B-Gg0            
X YtW)F#t }%S0    For Each Cell In rngActualSheetData.Cells51Testing软件测试网
u~|d!|M
          intRowIndex = Cell.Row
&LCH^H)}2T0          intColumnIndex = Cell.Column51Testing软件测试网 q6iV:Y#R0`$BB
          If Cell.Value <> rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Value Then
4a0d W0dN9RDc0              Cell.Font.Color = vbRed
J#f!RO y9\1L!^0              rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Font.Color = vbRed
`9a%G+o6Q@'l9e0              intAreDifferent = 1
0C7F:|NYh0          Else
MR,_XQ"Vn5o0              Cell.Font.Color = vbGreen
i7_5@Hp0              rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Font.Color = vbGreen51Testing软件测试网}*z2j2ahsOoH
          End If
Lq1T2{f&u0    Next51Testing软件测试网~Pr"s y'KsrW
H
    51Testing软件测试网mQd}SL%{
    If intAreDifferent = 0 Then51Testing软件测试网+nxD2]/NC;X[!j
          Reporter.ReportEvent 1, "Compare Excel Sheets in file: " & strFilePath, "There are differences between the actual and expected results, see Excel file for details"
_\0WzR$d0    End If
A7Hp_]L8Q0   
0]j$eI^VTpmo+b0    objExcelApplication.ActiveWorkbook.Save51Testing软件测试网2Kh E.[*}p,w
    objExcelApplication.Quit51Testing软件测试网;S2X9v0B&fuQ
    Set ōbjExcelApplication = Nothing
R7EFb1Xb"Bz/U1c0          51Testing软件测试网P/O#|[x'v|.v
End Sub51Testing软件测试网9zF%j4xrg"p