QTP实例-几个操作EXCEL的代码

-\}u&x9_
vf(B0列几个VBS操作Excel对象的代码，希望对有需要的朋友有所帮助

*A'ch dWe,^0
9{0p ^_#s N+w
U0Dim ExcelApp    'As Excel.Application51Testing软件测试网;N-x#f} T`
Dim excelSheet  'As Excel.worksheet51Testing软件测试网/L!k[&E/^whD
Dim excelBook   'As Excel.workbook
4s(k:diJF0Dim fso         'As scrīpting.FileSystemObject51Testing软件测试网*cCL-r)lF%_h

(zrau9g j#?0' *********************************************************************************************51Testing软件测试网1\6O4F5Xt5U_n&p
' 函数说明：创建一个Excel应用程序ExcelApp,并创建一个新的工作薄Workbook;51Testing软件测试网;]H"i'X0U*K;jD
' 参数说明：无
$}J3J7Ns4_0' 调用方法:
Mg;G l\pX!u0'           CreateExcel()
;UfG
PeDvI4H0' *********************************************************************************************

Function CreateExcel()
G ?|#S2g;o$L+R0    Dim excelSheet
d k ZJ;@C0    Set ExcelApp = CreateObject("Excel.Application")51Testing软件测试网A:YF }/pZ1b
    ExcelApp.Workbooks.Add 
X5LB McP0    ExcelApp.Visible = True51Testing软件测试网ZBc/MC a
    Set CreateExcel = ExcelApp
\r;_-ZDhx(e0End Function51Testing软件测试网&G!H,zW%Cy+N:N
 51Testing软件测试网.R2`9hJd7F2e
' *********************************************************************************************51Testing软件测试网9_GXOE L n
' 函数说明：关闭Excel应用程序;51Testing软件测试网2r ^;D|8J1}
' 参数说明：51Testing软件测试网 D)c-mj!b7^c?3Ck7L
'          (1)ExcelApp:Excel应用程序名称;
kuX.J*Cmq0' 调用方法:
F$m)pTC6Rb)U0'           CloseExcel(ExcelApp)
-c5y o9gS?'D0' *********************************************************************************************51Testing软件测试网|o4vcRs
Sub CloseExcel(ExcelApp)51Testing软件测试网W$u8\bI7c6Nh
    Set excelSheet = ExcelApp.ActiveSheet51Testing软件测试网 ]#[O-b"Vy$qS+\
    Set excelBook = ExcelApp.ActiveWorkbook
;@(R@Q:[5`5kKe0    Set fso = CreateObject("scrīpting.FileSystemObject")51Testing软件测试网3~h"MZ7V&ygo*p
    On Error Resume Next
s lw8totf#U0    fso.CreateFolder "C:\Temp"51Testing软件测试网lN!e\*L-b(r\
    fso.DeleteFile "C:\Temp\ExcelExamples.xls"
/Ga%[6|v7z0    excelBook.SaveAs "C:\Temp\ExcelExamples.xls"51Testing软件测试网G2UER-@ IZ h0?sc$f~
    ExcelApp.Quit51Testing软件测试网4Ev+Qh%SE9K:X
    Set ExcelApp = Nothing
"^
L jyr/_0    Set fso = Nothing
"YhNBY0    Err = 0
tB3v"?*FjB0    On Error GoTo 051Testing软件测试网a,x2r2m u5tW*Z|
End Sub
JwI%Iz%{e0 51Testing软件测试网*Zq2AZ/\&e8U
' *********************************************************************************************
1@(EMUb!SYD6j~0' 函数说明：保存工作薄;
} N}p]c2EW0' 参数说明：
\ NMhLL$FF0'          (1)ExcelApp:Excel应用程序名称;
V4[#t
AkP p!v&_%b0|\0'          (2)workbookIdentifier:属于ExcelApp的工作薄名称;51Testing软件测试网!xR}@ F M&K
'          (3)path:保存的路径;
Z h5M6b!tZ3^\f F/F0' 返回结果：51Testing软件测试网'PZ k+B}mP
'          (1)保存成功，返回字符串：OK
/F j'S4V$FAtJ0'          (2)保存失败，返回字符串：Bad Worksheet Identifier
_7Nbel0' 调用方法:
ziQ2~`rU/B!?Ex0'           ret = SaveWorkbook(ExcelApp, "Book1", "D:\Example1.xls")
B \|R(g ] [JM]0' *********************************************************************************************51Testing软件测试网O {M+~Gv!e;B:\

[uy"{ctPk0Function SaveWorkbook(ExcelApp, workbookIdentifier, path) 'As String
:Fu.? V-`6GTL&T0    Dim workbook51Testing软件测试网Idj[/Gf2o.e9\N
    On Error Resume Next  '启用错误处理程序
V1m\KR;a_(Ti0    Set workbook = ExcelApp.Workbooks(workbookIdentifier)
.`%ai/b"xe0    On Error GoTo 0   '禁用错误处理程序51Testing软件测试网T)g6f/ac/B

    If Not workbook Is Nothing Then51Testing软件测试网lIY4V/A8|Uu'W
        If path = "" Or path = workbook.FullName Or path = workbook.Name Then
;\0j yneK0            workbook.Save
,E{2c)U5ii0m0        Else
8a9a]]]8`T/k0            Set fso = CreateObject("scrīpting.FileSystemObject")
F&j~T l9x1hdi`'v0 
dL0X7wL[3q;P0            '判断路径中是否已添加扩展名.xls51Testing软件测试网]n*Gg6D]ch l
            If InStr(path, ".") = 0 Then
:p;E4v8{6D-^8k,E5h0                path = path & ".xls"51Testing软件测试网;M ?F*C1t_3XA3f` `h
            End If
PV;J z.]H'?j8n0 
l6F/f'QE.T5y,Q0            '删除路径下现有同名的文件
P9YR3g E;z.q0            On Error Resume Next
uwt%T5^3hK[7Z_0            fso.DeleteFile path51Testing软件测试网4c'Eg)f4n n;T
            Set fso = Nothing
@#x+VXp0            Err = 051Testing软件测试网z/{Y a|{r
            On Error GoTo 051Testing软件测试网 u-{"Y0q#S
           51Testing软件测试网VYH-t#c5U*c(c;L
            workbook.SaveAs path
M$^xK"p0K0        End If
)w S2_ i2X/DS~"B0V:Hh0        SaveWorkbook = "OK"51Testing软件测试网#r"ij"j0h O&?
    Else
mr2Tem0        SaveWorkbook = "Bad Workbook Identifier"
\s8N;D,f\0    End If51Testing软件测试网q@_ y$q-s]'C
End Function
8A/Z~1c&{0 
vth+`l9_{f0' *********************************************************************************************
C6ri7IxR3x0' 函数说明：设置工作表excelSheet单元格的值
bcw&M5lmR;y0' 参数说明：
3E1T}8a5h(|CI[s0'          (1)excelSheet:工作表名称;51Testing软件测试网0C9oPnf(P
'          (2)row:列的序号,第一列为1;
~2q,M!|-T5p` cZAl0'          (3)column:行的序号,第一行为1;
G&I!_LQ6`"{&a)N
L0'          (4)value:单元格要设置的值;
Y9Oo6ONtjU2\0' 返回结果：51Testing软件测试网h0m*|"Iua-A&W5I
'          无返回值51Testing软件测试网L1pH3M*F4q%AE
' 调用方法:51Testing软件测试网q]0phaG4Ts A
'           SetCellValue excelSheet1, 1, 2, "test"
&C+r7Y({h#O7@0' *********************************************************************************************

8AH)LdW`f0Sub SetCellValue(excelSheet, row, column, value)
#f P0^6N o7~wr0    On Error Resume Next
SK]7O8N-RN0    excelSheet.Cells(row, column) = value
%R+w*f(\5N(B0    On Error GoTo 0
h%e zKyR7F/X5y z-D0End Sub
{L/^FIe0 51Testing软件测试网,S!tbOnst9s
'The GetCellValue returns the cell's value according to its row column and sheet51Testing软件测试网+ls1A:{]3|
'excelSheet - the Excel Sheet in which the cell exists51Testing软件测试网,W)u!ze`]S
'row - the cell's row51Testing软件测试网@ f8bD(~m1q"o @ z3X
'column - the cell's column51Testing软件测试网A,oQNb\R*jU]+G
'return 0 if the cell could not be found
c7fx0H2z.^5qp H,W0' *********************************************************************************************
R4v'F:ybg0' 函数说明：获取工作表excelSheet单元格的值51Testing软件测试网pAd$i:Y.K
' 参数说明：
WU2F9s3Q&T0'          (1)excelSheet:工作表名称;
6\Z/][bY#lM(J0'          (2)row:列的序号;51Testing软件测试网t2h3u.f\ `I"zCB
'          (3)column:行的序号;51Testing软件测试网s:kh7Ne)^
' 返回结果：
8p2Z5dp6rtf0'          (1)单元格存在,返回单元格值;51Testing软件测试网 Mk1A z(CnW}
'          (2)单元格不存在，返回0;51Testing软件测试网|!D'Q_g1I-K"i9R
' 调用方法:
T(L'O lVl0'           set CellValue = GetCellValue(excelSheet, 1, 2)51Testing软件测试网MeV\W8_
' *********************************************************************************************

Function GetCellValue(excelSheet, row, column)
b8gp%g#I:k7V4LN0    value = 0
g4d r*an:P#bq"`0    Err = 051Testing软件测试网g \&^]G.nc
    On Error Resume Next
q,Ceo:qLQ@0    tempValue = excelSheet.Cells(row, column)
b5@&R}(zGTK0    If Err = 0 Then
w^9Tp*o7n0e0        value = tempValue
\6Wa$j C4z4Ps"cv0        Err = 051Testing软件测试网Y
pXy1]
    End If
7U(B(?Z O1D l1q0    On Error GoTo 0
6Bd&u8Nsc"ZN m0    GetCellValue = value
9zu#chw&g;o0End Function51Testing软件测试网0o2W!vhJK$eui MK
 51Testing软件测试网[.l ut!KI
' *********************************************************************************************
Sz!T"|hDm0' 函数说明：获取并返回工作表对象51Testing软件测试网zKvbiQ&Q
' 参数说明：51Testing软件测试网*d4{,g
D'Wj1uu
'          (1)ExcelApp:Excel应用程序名称;51Testing软件测试网YQ;jiXt
'          (2)sheetIdentifier:属于ExcelApp的工作表名称;
/o8]^*]k)?M&ZT\ J0' 返回结果：
,tqE5s4] kL|
I*E}0'          (1)成功：工作表对象Excel.worksheet
E*F,c!x@7c"c;B0'          (1)失败：Nothing51Testing软件测试网X Lt R+An
' 调用方法:51Testing软件测试网` m`3j
c d
'           Set excelSheet1 = GetSheet(ExcelApp, "Sheet Name")
3m4YI{FY0' *********************************************************************************************

1l/w~$C%B0Function GetSheet(ExcelApp, sheetIdentifier)51Testing软件测试网QJypG b0z,Y
    On Error Resume Next
Z*KW)t~+`0    Set GetSheet = ExcelApp.Worksheets.Item(sheetIdentifier)51Testing软件测试网HF ?wt$p
    On Error GoTo 051Testing软件测试网L${G8a+NQ"Jc I
End Function51Testing软件测试网B'zz%r8M,~j:Z9F
 51Testing软件测试网p?7|bd8A
' *********************************************************************************************51Testing软件测试网b7y:PX!L$UhM-sll!x2L(J
' 函数说明：添加一张新的工作表51Testing软件测试网/[k~Gv
' 参数说明：
WU:B:zh$u7\,K^ZO0'          (1)ExcelApp:Excel应用程序名称;51Testing软件测试网w$W3x Za?
'          (2)workbookIdentifier:属于ExcelApp的工作薄名称;51Testing软件测试网 c%o2C7P`OXJ*G3Z(~
'          (2)sheetName:要插入的工作表名称;51Testing软件测试网7b4?WkJ2Q
' 返回结果：
6qy{ ?m&H7Ki0'          (1)成功：工作表对象worksheet
{OLbV0'          (1)失败：Nothing51Testing软件测试网(RLH0@}
' 调用方法:
LrvHZ/g5f0'           InsertNewWorksheet(ExcelApp, workbookIdentifier, "new sheet")51Testing软件测试网^{+K`0a3~9~
' *********************************************************************************************

Function InsertNewWorksheet(ExcelApp, workbookIdentifier, sheetName)51Testing软件测试网I%y*UkXV$q'PU
    Dim workbook 'As Excel.workbook
KlKf R!Q"H0    Dim worksheet 'As Excel.worksheet51Testing软件测试网vd-?5J4~5c.W]q'E
 
%m5I*Y:J PiM;[ XNZL0    '如果指定的工作薄不存在，将在当前激活状态的工作表中添加工作表
X$h!H0{g/VckE0    If workbookIdentifier = "" Then51Testing软件测试网Bd7@"uSm
        Set workbook = ExcelApp.ActiveWorkbook
,c*`S0|S2Q)NL0    Else
!da3n0I3Lb0        On Error Resume Next51Testing软件测试网`u}G3i+n0n#U
        Err = 0
4u#ZM\qs~0        Set workbook = ExcelApp.Workbooks(workbookIdentifier)51Testing软件测试网1n%CY3R*J6HKk
        If Err <> 0 Then51Testing软件测试网/Cxq+Xth"f"Z.@
            Set InsertNewWorksheet = Nothing
)i S gfsA[0            Err = 051Testing软件测试网7f}6[ aF0L-~
            Exit Function
K~(DE;\{Cc p0        End If
kJ-?a;N0        On Error GoTo 051Testing软件测试网*{C$c&R3e;\$t*e
    End If51Testing软件测试网 pKG reUW
 51Testing软件测试网#CX5\7](MU
    sheetCount = workbook.Sheets.Count  '获取工作薄中工作表的数量
cyND;pLe.R0    workbook.Sheets.Add , sheetCount '添加工作表51Testing软件测试网$SEK"gx3d8U
N OCE
    Set worksheet = workbook.Sheets(sheetCount + 1)  '初始化worksheet为新添加的工作表对象
G3e2a.z/mhXw8fI c0 
7\uKB.t!fs!h0    '设置新添加的工作表名称
~b9FP^0D0    If sheetName <> "" Then
Z9FN;cTzm*y&c5Vu p0        worksheet.Name = sheetName
z,JJ+Kl/p0    End If51Testing软件测试网_?
Y+bX(K*x'A
 
9@K EA)X6h{GEX0    Set InsertNewWorksheet = worksheet51Testing软件测试网fytC:{']4m
End Function
"R8_W9joJ0 51Testing软件测试网j;` ct5Go]
' *********************************************************************************************
u bfr m?0' 函数说明：修改工作表的名称;51Testing软件测试网S$vE
kV?f
' 参数说明：51Testing软件测试网!i1Dq-^9v
'          (1)ExcelApp:Excel应用程序名称;51Testing软件测试网o7y6S[(r}N&YH
'          (2)workbookIdentifier:属于ExcelApp的工作薄名称;
+P0w9J2NM0'          (3)worksheetIdentifier:属于workbookIdentifier工作薄的工作表名称;
8sL8M!A'c0'          (4)sheetName:修改后的工作表名称;
B0bra de;Xs0' 返回结果：
c"^ i#dH]|1u;d j0'          (1)修改成功，返回字符串：OK51Testing软件测试网"phY `O:`&@*I6u~
'          (2)修改失败，返回字符串：Bad Worksheet Identifier51Testing软件测试网{M4Lt(o"AY'r
' 调用方法:51Testing软件测试网q1wi2GB
'           set ret = RenameWorksheet(ExcelApp, "Book1", "Sheet1", "Sheet Name")
3z2C#x8] jp0' *********************************************************************************************

Function RenameWorksheet(ExcelApp, workbookIdentifier, worksheetIdentifier, sheetName)51Testing软件测试网5S
S,~"`&H"q/Bk#Q5M
    Dim workbook
0g.aG.oW ck3{x0    Dim worksheet
t.|(wt
c3t0    On Error Resume Next
)d6t!FmA,b t {F0    Err = 051Testing软件测试网'\6uyYH;A
    Set workbook = ExcelApp.Workbooks(workbookIdentifier)
%t{+f;dm!I0    If Err <> 0 Then
YT9f,l&A
{0        RenameWorksheet = "Bad Workbook Identifier"
aY+F"T7K4RBT(Ym n0        Err = 0
;z9aS6c ~1r0        Exit Function51Testing软件测试网t j GT S\$Mb9cn/k
    End If51Testing软件测试网L H4^:D#U.I
a5V
    Set worksheet = workbook.Sheets(worksheetIdentifier)

W"I%dW%x+u o.n0    If Err <> 0 Then
,_"d/{ Y$a{X0        RenameWorksheet = "Bad Worksheet Identifier"
| w7K/p?f,m0        Err = 0
,{!mjV:_E(XE0        Exit Function
TT7lz-n{.l/E0    End If
y6}o$@}ES0    worksheet.Name = sheetName51Testing软件测试网e z0F]Lj
    RenameWorksheet = "OK"
0IiR-^ Vl:_b0End Function
#O%i?;Q$xdrYj&X0 
'zW-|y+w A0' *********************************************************************************************51Testing软件测试网1r'B'n'Z6`z
' 函数说明：删除工作表;
Aa
Ub U9W"d}*EX0' 参数说明：51Testing软件测试网%k9V Ykv,vm6Bn
'          (1)ExcelApp:Excel应用程序名称;51Testing软件测试网oU9@/poy
'          (2)workbookIdentifier:属于ExcelApp的工作薄名称;
x&i?0F~5\#X0'          (3)worksheetIdentifier:属于workbookIdentifier工作薄的工作表名称;
vP&n1QL@5fg1sK0' 返回结果：51Testing软件测试网!LD},|7e,be
'          (1)删除成功，返回字符串：OK51Testing软件测试网^WD:d3T5N
'          (2)删除失败，返回字符串：Bad Worksheet Identifier51Testing软件测试网/RxucZ0Rv:A
' 调用方法:
"J/lAm4w,U'qU\0'           set ret = RemoveWorksheet(ExcelApp, "Book1", "Sheet1")
;d:NL!\)Z0|0' *********************************************************************************************51Testing软件测试网n ^W/Y&Hw

Function RemoveWorksheet(ExcelApp, workbookIdentifier, worksheetIdentifier)51Testing软件测试网6_
Wj`*dLL
    Dim workbook 'As Excel.workbook51Testing软件测试网.u4^:r'x5`
    Dim worksheet 'As Excel.worksheet
%v3y/gi{r&u P0    On Error Resume Next
.D1n[+aG%h0    Err = 0
qMV'Y5~%i`GiF0    Set workbook = ExcelApp.Workbooks(workbookIdentifier)51Testing软件测试网dW3OJ7oX
    If Err <> 0 Then51Testing软件测试网-D"]L ?l{F
        RemoveWorksheet = "Bad Workbook Identifier"51Testing软件测试网y(K#WJqf
        Exit Function51Testing软件测试网*|)_*GJXFP~o
    End If51Testing软件测试网N!C&\0ES7\
    Set worksheet = workbook.Sheets(worksheetIdentifier)
y
f d%Sk ~2j(hu"C0    If Err <> 0 Then
7iX3g2VU2J0        RemoveWorksheet = "Bad Worksheet Identifier"
k T5Ef1FD5m0        Exit Function
/w*G i.w5i(Y6s0    End If51Testing软件测试网R |n!]_'?U
    worksheet.Delete51Testing软件测试网yIZ'O2DoG7Xn'{
    RemoveWorksheet = "OK"51Testing软件测试网4Ab(Hr-l5JA
End Function51Testing软件测试网%{@Lm4I$ZSk
 
a T(f-ptE^R+_y0' *********************************************************************************************51Testing软件测试网9X`;[_m4s\#y G
' 函数说明：添加新的工作薄51Testing软件测试网5O
B7VJGf'w0y
' 参数说明：
SC
?5_~-tA XN3[@L0'          (1)ExcelApp:Excel应用程序名称;
5?$NY]8|QxZ,R g0' 返回结果：
]/kO.u/{^!]w0'          (1)成功：工作表对象NewWorkbook51Testing软件测试网;b6c(o;[;pC
'          (1)失败：Nothing
3a6]Ikbl6T-kYy0' 调用方法:51Testing软件测试网`-?RVR`2s$YHL
'          set NewWorkbook = CreateNewWorkbook(ExcelApp)
!S;wtR m"K2k0' *********************************************************************************************51Testing软件测试网 `L)v3fG%X

Function CreateNewWorkbook(ExcelApp)51Testing软件测试网!k*S&m+bMg\
    Set NewWorkbook = ExcelApp.Workbooks.Add()
c F-ZM%~0    Set CreateNewWorkbook = NewWorkbook
WPi{P'|"U7HOCM0End Function51Testing软件测试网F'}oA$B
 
-X$oZ,R,X#qBP.l0' *********************************************************************************************
U9bB
j@/iS0' 函数说明：打开工作薄
X+sv`8gf0' 参数说明：
+wQ,GG}%vW0'          (1)ExcelApp:Excel应用程序名称;51Testing软件测试网Mf2E+U#@~l
'          (2)path:要打开的工作薄路径;
sg+rGf.G/wWId0' 返回结果：51Testing软件测试网 c:h4f9b%Taw;X6d
'          (1)成功：工作表对象NewWorkbook51Testing软件测试网!H"\ D.blTM7@/g
'          (1)失败：Nothing51Testing软件测试网cvq2W(`S-N1Q
' 调用方法:51Testing软件测试网6F9_+@i_8s7NGhR
'          set NewWorkbook = CreateNewWorkbook(ExcelApp)
,C.pjg)w%n,lT'v0' *********************************************************************************************

t m6`2P ^9p0Function OpenWorkbook(ExcelApp, path)
J5h
c5i&NXMz$dH$QXL;?0    On Error Resume Next51Testing软件测试网HM&\*t*w
    Set NewWorkbook = ExcelApp.Workbooks.Open(path)
&{9y;Jv
hwM4I;W0    Set ōpenWorkbook = NewWorkbook
L&lAP1pf#AxB0    On Error GoTo 051Testing软件测试网bt{*^N'|:In
End Function
$H*bm)@z!P:q7V0 51Testing软件测试网N*|:Tn zp}um
' *********************************************************************************************
$p#G$?NJ5v%z;M0' 函数说明：将工作薄设置为当前工作状态51Testing软件测试网CO;?q"o6OU
' 参数说明：51Testing软件测试网"@$DaK!U"| G}0g
'          (1)ExcelApp:Excel应用程序名称;
'z#l ?oI:v;]%Qj/vt0'          (2)workbookIdentifier:要设置为当前工作状态的工作薄名称;51Testing软件测试网rr4SL1h*Nf2L
' 返回结果：无返回值;51Testing软件测试网6P)^uwkL0Y.VD;i
' 调用方法:51Testing软件测试网YCXo#SW+q
'          ActivateWorkbook(ExcelApp, workbook1)
)CRpF*f$] v7x0' *********************************************************************************************51Testing软件测试网 moq5dI7x

Sub ActivateWorkbook(ExcelApp, workbookIdentifier)51Testing软件测试网%l;|b)NpAi@p i
    On Error Resume Next
H1[O\"\&{:PEq0    ExcelApp.Workbooks(workbookIdentifier).Activate
Ti i"N8@ n0    On Error GoTo 051Testing软件测试网HV&tsIV
End Sub51Testing软件测试网/B(QVwe.G n)G&?'h
 
2OQq M9P6E0' *********************************************************************************************
*K+k;Y3lh0' 函数说明：关闭Excel工作薄;51Testing软件测试网2|%P5V7H.w-s-v;e
' 参数说明：51Testing软件测试网ui:D6D2x:av
'          (1)ExcelApp:Excel应用程序名称;
F)S;Wc`!M3D.O@i0'          (2)workbookIdentifier:51Testing软件测试网;rO#u[ hmZUY
' 调用方法:51Testing软件测试网BS1rk9i
'           CloseWorkbook(ExcelApp, workbookIdentifier)51Testing软件测试网-U_} {-A(`s
' *********************************************************************************************51Testing软件测试网)ufU9|td2a'MB

Sub CloseWorkbook(ExcelApp, workbookIdentifier)51Testing软件测试网KBHKMDW;_p
    On Error Resume Next51Testing软件测试网[.D Qc0hU7@*c%S
    ExcelApp.Workbooks(workbookIdentifier).Close51Testing软件测试网XZ(v6Ci? a
    On Error GoTo 051Testing软件测试网8O m'PTsOIrb
End Sub51Testing软件测试网J&Zn2`(`v,l!cRL
 51Testing软件测试网`7F%?:n%xi9EbL
' *********************************************************************************************
Z%O9HX xx9bllm0' 函数说明：判断两个工作表对应单元格内容是否相等
W"^r8I'i.vt KK0' 参数说明：51Testing软件测试网O ^8U F/n}*kZ/}
'          (1)sheet1:工作表1的名称;51Testing软件测试网Ft
|z{~0X6r
'          (2)sheet2:工作表2的名称;51Testing软件测试网`o0w j#\5t!j)b/a
'          (3)startColumn:开始比较的行序号;
ci6F/\-Q b n7W"^0'          (4)numberOfColumns:要比较的行数;51Testing软件测试网4dL*g
rJ;@m#I*fq
'          (5)startRow:开始比较的列序号;51Testing软件测试网hi}p*PJ"E
'          (6)numberOfRows:要比较的列数;51Testing软件测试网9{$Cd` Cc
'          (7)trimed:是否先除去字符串开始的空格和尾部空格后再进行比较,true或flase;
x)_7{"B_0' 返回结果：
$M:N(E8{v0'          (1)两工作表对应单元格内容相等：true51Testing软件测试网GG \$R*~y"I{'Y7@
'          (2)两工作表对应单元格内容不相等:flase        
$}_6@ {`0' 调用方法:
SA`E6u|0'           ret = CompareSheets(excelSheet1, excelSheet2, 1, 10, 1, 10, False)
*W3W3G l}0' *********************************************************************************************

C0tU0O3e7\"BYEXSk0Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed)
]$Ab5tzk2Q0    Dim returnVal 'As Boolean51Testing软件测试网/e6gyP%a#}JHE9r
    returnVal = True
*Q7w+n2a?
x9c*y|0 
"\ZN*j1G+E5oF g0    '判断两个工作表是否都存在,任何一个不存在停止判断，返回flase51Testing软件测试网9@+Lr~-B3R,Y
    If sheet1 Is Nothing Or sheet2 Is Nothing Then51Testing软件测试网7IS|oKo;H
        CompareSheets = False51Testing软件测试网Bf`j8S
        Exit Function51Testing软件测试网hJ"R.O[9I
    End If51Testing软件测试网1kt/L O6A.N2_Yf6JB
 
4s0NVc$K#@
A+h0    '循环判断两个工作表单元格的值是否相等
tk0VqG4h0    For r = startRow to (startRow + (numberOfRows - 1))
2gwbrA0        For c = startColumn to (startColumn + (numberOfColumns - 1))
%Mt4y"FX7Jk1p0            Value1 = sheet1.Cells(r, c)
hA1Zo'YHOLW0            Value2 = sheet2.Cells(r, c)
I+tviN;Fs0 
|:Zq3_^"j;N0            '如果trimed为true,去除单元格内容前面和尾部空格51Testing软件测试网DdC-X#^7FQh
            If trimed Then
/R"N(L Z7mK/]0                Value1 = Trim(Value1)
[d@-YE*]?0                Value2 = Trim(Value2)51Testing软件测试网7e-^/p]:P0hX
            End If51Testing软件测试网)d;Tz0[n
 
1xG\EV1?$e:q0            '如果单元格内容不一致,函数返回flase
y X}?6\obT0            If Value1 <> Value2 Then51Testing软件测试网W|1w9Z7^C K+|
                Dim cell 'As Excel.Range
-LL1{(F*R!G0                '修改sheet2工作表中对应单元格值51Testing软件测试网 t4TM*t M.uw'z
                sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'." 
T!GO(PD1{+g0                '初始化cell为sheet2中r:c单元格对象51Testing软件测试网`D8h5|'xF!k
                Set cell = sheet2.Cells(r, c) '
X'L(ODz z-C4M2C2?.d0                '将sheet2工作表中对应单元格的颜色设置为红色51Testing软件测试网Te`0t%J2q k
                cell.Font.Color = vbRed 
!ov.P3n q@#P5k0                returnVal = False
*{lZv{$|Huj!c0            End If
-G6p H-K7~UJ9j0        Next51Testing软件测试网dEn?0ubC
    Next51Testing软件测试网~!J R7e(cwNo(c
    CompareSheets = returnVal
q*f1g7Pu6VS.f0End Function