对比excel中两个sheet中的数据

Option Explicit51Testing软件测试网 zj4DZEn
'**********************************************************51Testing软件测试网9y*KLjv,~m|W
'目的:   发现Excel中两个Sheet中的数据是否不同并打标记
1}RD"LAm _1~*z4h0'           绿色 - 匹配
%e^\#J_t$]0'           红色 - 不匹配
PuJ ~kt.Ep0'          51Testing软件测试网(M1a1Z:haWcED/L9g
'参数:    strFilePath51Testing软件测试网*qjd-B-T#o
'         strFirstSheetName51Testing软件测试网p+^tI5O
'         strSecondSheetName
0P:na(z|Ag7_8B0'**********************************************************51Testing软件测试网SX E$E+lkT

Sub Compare2ExcelSheetsInTheSameFile(strFilePath, strFirstSheetName, strSecondSheetName) 51Testing软件测试网3KUp%E%y'd"c
   
+q[M?t^F0    Dim   objExcelApplication
"k:@I1sI8Y*Z0     51Testing软件测试网x4_z$g?'s}5~
    Dim   rngActualSheetData   'Will hold a MS Excel range object51Testing软件测试网i6z?"vW6m-v$wRM
    Dim   rngExpectedSheetData51Testing软件测试网?$ax^m` VAV&^?
      
j u [4cL|lr4y1d,r0    Dim   intRowIndex51Testing软件测试网B+R)s9I$ja
    Dim   intColumnIndex51Testing软件测试网m!p)t6M#g1l
   
o|m-^UI3pr0    Dim   intAreDifferent51Testing软件测试网7xwt.F1\@ o%F6J
    51Testing软件测试网uLQ1E0|M
    intAreDifferent = 1       '1 - there are no diferences between the sheets, 0 - the sheets are different
-r+z+c\ s0    51Testing软件测试网t)BRi+_;YS*X{
    Set ōbjExcelApplication = CreateObject("Excel.Application")
?Ks3uM;{ pW6G6o0    objExcelApplication.Workbooks.open (strFilePath)
+Q,_ Nxl9D)oFSD0     51Testing软件测试网jeA6u'zz:U-t"Y
    Set rngActualSheetData = objExcelApplication.Worksheets(strFirstSheetName).UsedRange
A s0_-RJGpn0    Set rngExpectedSheetData = objExcelApplication.Worksheets(strSecondSheetName).UsedRange51Testing软件测试网%|jM.J
Sz;P
 51Testing软件测试网JhQ
_|!u
    'Initiallization of the sheets so that it will be possible to understand what went wrong
N:L'c3zI vk}1p0    objExcelApplication.Worksheets(strFirstSheetName).UsedRange.Font.Color = vbBlack51Testing软件测试网5XZ_Q(~;h
    objExcelApplication.Worksheets(strSecondSheetName).UsedRange.Font.Color = vbBlack
CK&M;R(mt
lN0             51Testing软件测试网 MeO#jP&{
    For Each Cell In rngActualSheetData.Cells51Testing软件测试网)jq5o R3QDE?
          intRowIndex = Cell.Row
~C1p3ZWh0          intColumnIndex = Cell.Column51Testing软件测试网kCSe~_
          If Cell.Value <> rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Value Then
9Y&X}Qo'z;OS4JI0              Cell.Font.Color = vbRed51Testing软件测试网OIN,]2|D
              rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Font.Color = vbRed
0cpv"^)?M*qa0              intAreDifferent = 151Testing软件测试网iR@B"stE$aZ
          Else
(\ Fz"_#P0              Cell.Font.Color = vbGreen51Testing软件测试网m'i2?"`B;wY
              rngExpectedSheetData.Cells(intRowIndex, intColumnIndex).Font.Color = vbGreen
$eq h^e;_ n0          End If
9{{/xz]Ex8K0    Next51Testing软件测试网%c`A.g VPRm
   
X}-vT.Xt9w1A8O]0    If intAreDifferent = 0 Then51Testing软件测试网CS:j1b~3U$n!B
          Reporter.ReportEvent 1, "Compare Excel Sheets in file: " & strFilePath, "There are differences between the actual and expected results, see Excel file for details"
:gI&mVEf8B5w0    End If
:v.{%HGO
s0Y(c Q
s0    51Testing软件测试网#fj*p5cN u2J;a@
    objExcelApplication.ActiveWorkbook.Save
L B%r f9D8G"w0    objExcelApplication.Quit
%s']*p7_?)T4v6C0    Set ōbjExcelApplication = Nothing
6U!lh_7lc0          51Testing软件测试网)[+iBE.w$V
End Sub