利用VBA通过Excel数据交互实现模版的自动填写

1、利用 vba 通过exce l数据交互实现模版的自动填写毛战军(长江大学 一年级工作部 ,湖北 荆州 434023)摘 要 利用 o ffice办公系统组件中的 exce l组件及 vba 语言自动分割数据 ,自动完成班级成绩表的填写 .关键词 vba; exce l;数据交互中图分类号 tp391. 13文献标识码 a文章编号 1671 - 5330 ( 2010) 02 - 0037 - 060 引言在工作中 ,需要从流水阅卷得到的学生成绩 总表中遴选出所需要的各班级成绩来制作统一格 式的班级成 绩表 . 成 绩模 版 以 exce l工 作表 的 形 式保存. 在制作过程中若只是使用手工

2、操作 ,则工 作强度大 ,工作效率低. 可以考虑利用 o ffice 办公 系统组件中的 exce l工作表及 vba 语言自动分割 数据 ,自动完成各班级成绩表 ,提高工作效率 , 降 低工作强度 .vba (v isua l b a sic fo r app lica tion)是 m ic ro softo ffice系列软件的内置编程语言 ,用来为 m ic ro soft o ffice套件提供 一致 的应 用程 序 开发 . vba 编 程 语言与 v isua l b a sic编程语言互相兼容 ,但不能在 一个环境中独立运行 ,需要宿主应用程序支持它 的功能特性 1 .1 系统

3、分析学生成绩总表用 exce l文档保存 , 如图 ( 1 ) . 在总表中已经按学生姓名的顺序排好 ,学生的成 绩已经登录完毕. 在 exce l工作表中通过 vba 语 句使用由 exce l对象模型所提供的对象的方法和 属性来操作 exce l文档 .1. 1 读取一个班级的学生人数通过循环语句来记录一个班级学生的人数 、 学生成绩所在工作表中的位置. 利用比较语句判断是否是同一班级学生来终止循环. 如以下代码 所示 :图 1d im studen tzongshu a s in tege r 定义 学 生总 人数 变量d im studen tn u a s in tege r 定义班

4、级总人数变量studen tzongshu = 1第一行为标题栏第一行为标题栏studen tn u = 1a c tiveshee t. r ange ( " b " & studen tzongshu +1 ) . se lec t选择工作表中 b列第 2 个单元格通过循环判断一个班级的人数收稿日期 2010 - 01 - 17作者简介 毛战军 ( 1970 )男 ,湖北仙桃人 ,讲师 ,硕士 ,主要从事 ca i应用与函数论教学与研究工作 .do w h ile r ange ( " b " & studen tzongshu +w

5、 o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " c " & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " e" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " d " &

6、 l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " g" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " e " & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( &q

7、uot; h" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " f" & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " i" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts (

8、1 ) .r ange ( " g" & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " j" & l + y) . v a luen extif studen tn u > 25 then y = 25 + yfo r l = 1 to studen tn u - 25w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( &q

9、uot; h " & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " c" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " i" & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts (

10、 1 ) . r ange ( " d" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " j" & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " e" & l + y) . v a lue w o rkbook s ( " c jb. xls &qu

11、ot; ) . shee ts ( 1 ) .r ange ( " k " & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " g" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " l" & l + 9 ) . v a lue = w o rkbook s (

12、"总 表. xls" ) . shee ts ( 1 ) . r ange ( " h" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " m " & l + 9 ) . v a lue = w o rkbook s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & l + y) . v a luew

13、o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " n " & l + 9 ) . v a lue = w o rkbook s ( "总 表. xls" ) . shee ts ( 1 ) . r ange ( " j" & l + y) . v a luen ext e lse end if填写院系名称1 ) . v a lue = r ange ( " b" & studen tzongshu + v

14、a lue 循环语句结束判断条件2 ) .studen tzongshu = studen tzongshuloop+ 1studen tzongshustuden tn u = studen tzongshu+1studen tzongshu-studen tn u得到一个班级的学生人数1. 2打开 exce l模版在 vba 中可以先定义 exce l对象变量 ,然后 再用 ge to b jec t ( ) 或者 c rea teo b jec t ( )函数返回一个 exce l app lica tion 对象 ,打开作 ,如以下代码所示 :打开 exce l模版exce l文档进行

15、操d im exlapp a s o b jec t 定义o n e rro r r e sum e n extse t exlapp = ge to b jec t ( ,exce l对象变量忽略错误" exce l. app lica2tion" ) 获得一个 exce l进程外对象if e rr. n um be r < > 0 then 如果 程外对象没有创建 ,则创建一个exce l进= c rea teo b jec t ( " exce l. ap 2se t exlappp lica tion" )e lseend ife r

16、r. c lea r清除错误exlapp. w o rkbook s. op en " d: m y docum en ts 2 c jb. xls" 打开 exce l文档= true 设为可见exlapp. w indow s. v isib le1. 3填写成绩模版通过循环语句实现工作表格的填写判断填写成绩所需要的列数if studen tn u < = 25 then n = studen tn ue lsen = 25end if填写成绩fo r l = 1 to nw o rkbook s ( " c jb. xls " ) .shee

17、 ts ( 1 ) .r ange ( " a " & l + 9 ) . v a lue = w o rkbook s ( "总表 . xls" ) . shee ts ( 1 ) . r ange ( " c" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " b " & l + 9 ) . v a lue = w o rkbook s ( "总 表 .

18、 xls" ) . shee ts ( 1 ) . r ange ( " d" & l + y) . v a luew o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( "90 分到 100分的学生人数w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( " d37 " ) . v a lue = w o rk shee tfunc tion. coun t if (w

19、 o rk2 book s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & stu2 den tzongshu - studen tn u + 1 & " : i" & studen t2c5 " ) . v a lue = w o rkbook s ( " 总 表 . xls" ) .( 1 ) . r ange ( " a" & studen tzongshu) . v a lue填写班级名称w o r

20、kbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( "shee tsf5 " ) . v a lue = w o rkbook s ( " 总 表 . xls" ) .( 1 ) . r ange ( " b" & studen tzongshu) . v a lue填写主讲教师名称w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( "zongshu ) , &qu

21、ot; > = 80 " )shee ts- w o rk shee tfunc tion.coun t if (w o rkbook s ( " 总 表 . xls " ) . shee ts ( 1 ) .r ange ( " i" & studen tzongshu - studen tn u + 1 & " : i" & studen tzongshu) , " > = 90 " )80 分到 89分的学生人数w o rkbook s ( " c jb

22、. xls" ) . shee ts ( 1 ) . r ange ( " d38 " ) . v a lue = w o rk shee tfunc tion. coun t if (w o rk2 book s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & stu2 den tzongshu - studen tn u + 1 & " : i" & studen t2c6 " ) . v a lue = w o rkb

23、ook s ( " 总 表 . xls" ) .( 1 ) . r ange ( " f" & studen tzongshu) . v a lue1. 4成绩分析在成绩表上要求填写本班级期末总评的成绩 分析. 如 图 ( 2 ) . 可 以 考 虑 在 模 版 上 利 用 公 式 完 成 ,如图 ( 3 ) . 也可以通过 vba 编程实现 :shee tszongshu ) , " > = 70 " )- w o rk shee tfunc tion.coun t if (w o rkbook s ( "

24、总 表 . xls " ) . shee ts ( 1 ) .r ange ( " i" & studen tzongshu - studen tn u + 1 & " : i" & studen tzongshu) , " > = 80 " )70 分到 79分的学生人数w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( " d39 " ) . v a lue = w o rk shee tf

25、unc tion. coun t if (w o rk2book s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & stu2den tzongshu - studen tn u + 1 & " : i" & studen t2zongshu ) , " > = 60 " )- w o rk shee tfunc tion.coun t if (w o rkbook s ( " 总 表 . xls " ) . shee

26、 ts ( 1 ) .r ange ( " i" & studen tzongshu - studen tn u + 1 & " : i" & studen tzongshu) , " > = 70 " )60 分到 69分的学生人数w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( " d40 " ) . v a lue = w o rk shee tfunc tion. coun t if (w o

27、rk2 book s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & stu2den tzongshu - studen tn u + 1 & " : i" & studen t2zongshu) , " < 60 " ) (w o rkbook s ( "总表.& studen tzongshu - w o rk shee tfunc tion. coun t ifxls" ) . shee ts ( 1 )

28、 . r ange ( " i" studen tn u + 1 & " : i" & stu2den tzongshu) , " < 0 " )不及格的学生人数w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( "填写成绩分析表w o rkbook s ( " c jb. xls" ) . shee ts ( 1 ) . r ange ( " d36 " ) . v a lue =

29、 w o rk shee tfunc tion. coun t if (w o rk2book s ( "总表. xls" ) . shee ts ( 1 ) . r ange ( " i" & stu2c41 " ) .v a lue=w o rk shee tfunc tion.a ve ragexls" ) . shee ts ( 1 ) . r ange ( " i" studen tn u + 1 & " : i" & stu2(w o rkbook s ( &

30、quot;总表.& studen tzongshu - den tzongshu) )班级均分1. 5保存文档den tzongshu - studen tn uzongshu ) , " > = 90 " )+ 1 & " : i" & studen t2-w o rk shee tfunc tion.xls " ) . shee ts ( 1 ) .- studen tn u + 1 &coun tif (w o rkbook s ( " 总 表 .r ange ( " i"

31、; & studen tzongshu" : i" & studen tzongshu) , " > 100 " )将已经制作完成的成绩表用班级名称另存 ,并关闭本文档w o rkbook s ( " c jb. xls" ) . savea s f ilenam e: = " d: m y docum en ts 2 " & w o rkbook s ( " 总 表 . xls" ) . shee ts ( 1 ) . r ange ( " b &quo

32、t; & studen tzongshu ) .v a luew o rkbook s (w o rkbook s ( "总 表 . xls" ) . shee ts( 1 ) . r ange ( " b " & studen tzongshu ) . v a lue & ".d im l a s in tege rd im y a s in tege r y = 1studen tzongshu = 1studen tn u = 1第一行为标题栏第一行为标题栏a c tiveshee t. r ange ( &quo

33、t; b " & studen tzongshu +1 ) . se lec t选择工作表中 b列第 2个单元格do w h ile a c tiveshee t. r ange ( " b " & studen t2zongshu + 1 ) . v a lue < > 循环语句结束判断条 件通过循环判断一个班级的人数do w h ile r ange ( " b " & studen tzongshu +1 ) . v a lue = r ange ( " b" & stude

34、n tzongshu + 2 ) .关闭文档= no th ingxls" ) . c lo sese t exlapp清空变量工作流程见图 ( 5 )2循环语句结束判断条件v a luestuden tzongshu = studen tzongshu + 1loopstuden tzongshu = studen tzongshu + 1studen tn u = studen tzongshu -studen tn u得到一个班级的学生人数定义 exce l对d im exlapp a s o b jec t象变量忽略错误o n e rro r r e sum e n exts

35、e t exlapp = ge to b jec t ( , " exce l. app lica2获得一个 exce l进程外对象if e rr. n um be r < > 0 thense t exlapp = c rea teo b jec t ( " exce l. ap 2tion" )如果 exce l进程外对象没有创p lica tion" )建 ,则创建一个e lseend ife rr. c lea r清除错误exlapp. w o rkbook s. op en " d: m y docum en ts 打开

36、exce l文档2 c jb. xls"exlapp. w indow s. v isib le = true 设为可见判断填写成绩所需要的列数if studen tn u < = 25 then n = studen tn ue lsen = 25end if填写成绩fo r l = 1 to nw o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " a " & l + 9 ) . v a lue = w o rkbook s ( "总图 5程序源代码3s

37、ub a ( )d im studen tzongshu a s生总人数变量定义学in tege r定义班级d im studen tn u a s in tege r总人数变量d im n a s in tege r表 . xls" ) . shee ts ( 1 ) . r ange ( " c" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " b " & l + 9 ) . v a lue = w

38、 o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " d" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " c " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " e" & l

39、 + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " d " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " g" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( &quo

40、t; e " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " h" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " f" & l + 9 ) . v a lue = w o rkbook s ( "总表 . xls" ) . shee ts (

41、1 ) . r ange ( " i" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " g" & l + 9 ) . v a lue = w o rkbook s ( "总n exte lseend if填写院系名称w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " c5 " ) . v a lu

42、e = w o rkbook s ( "总表 . xls" ) .shee ts ( 1 ) . r ange ( " a" & studen tzongshu) . v a lue填写班级名称w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " f5 " ) . v a lue = w o rkbook s ( "总表 . xls" ) .shee ts ( 1 ) . r ange ( " b" &

43、amp; studen tzongshu) . v a lue填写主讲教师名称w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " c6 " ) . v a lue = w o rkbook s ( "总表 . xls" ) . shee ts ( 1 ) . r ange ( " f" & studen tzongshu) . v a lue填写成绩分析表w o rkbook s ( " c jb.xls " ) . s

44、hee ts ( 1 ) .w o rk shee tfunc tion. xls " ) . shee ts ( 1 ) .- studen tn u + 1 &r ange" d36 " ) . v a lue=(表 . xls" ) . shee ts ( 1 ) . r ange ( " j"n extif studen tn u > 25 then y = 25 + yfo r l = 1 to studen tn ucoun t if (w o rkbook s ( " 总 表 .r ange (

45、" i" & studen tzongshu& l+y) . v a lue" : i" & studen tzongshu) , " > = 90 " ) - w o rk she2e tfunc tion. coun t if ( w o rkbook s ( " 总 表 . xls " ) . shee ts ( 1 ) . r ange ( " i" & studen tzongshu -stu2 den tn u + 1 & "

46、: i " & studen tzongshu ) , " >100 " )90 分到 100分的学生人数- 25w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " h " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " c" & l + y) . v a luew o rkbo

47、ok s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " i" & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " d" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " j" & l + 9 )

48、 . v a lue = w o rkbook s ( "总表 . xls" ) . shee ts ( 1 ) . r ange ( " e" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " k " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " g&

49、quot; & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r ange ( " l" & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " h" & l + y) . v a lue w o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) .r

50、 ange ( " m " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls" ) . shee ts ( 1 ) . r ange ( " i" & l + y) . v a luew o rkbook s ( " c jb. xls " ) . shee ts ( 1 ) . r ange ( " n " & l + 9 ) . v a lue = w o rkbook s ( "总 表 . xls"

51、) . shee ts ( 1 ) . r ange ( " j" & l + y) . v a luew o rkbook s ( " c jb.xls " ) . shee ts ( 1 ) .w o rk shee tfunc tion. xls " ) . shee ts ( 1 ) .- studen tn u + 1 &r ange" d37 " ) . v a lue=(coun t if (w o rkbook s ( " 总 表 .r ange ( " i" &

52、amp; studen tzongshu" : i" & studen tzongshu) , " > = 80 " ) - w o rk she2e tfunc tion. coun t if ( w o rkbook s ( " 总 表 .shee ts ( 1 ) . r ange ( " i" & studen tzongshu den tn u + 1 & " : i" & studen tzongshu ) ,90 " )80 分到 89分的学生

53、人数xls " ) .-stu2" > =w o rkbook s ( " c jb.xls " ) . shee ts ( 1 ) .w o rk shee tfunc tion. xls " ) . shee ts ( 1 ) .- studen tn u + 1 &r ange" d38 " ) . v a lue=(coun t if (w o rkbook s ( " 总 表 .r ange ( " i" & studen tzongshu" : i&q

54、uot; & studen tzongshu) , " > = 70 " ) - w o rk she2e tfunc tion. coun t if ( w o rkbook s ( " 总 表 .shee ts ( 1 ) . r ange ( " i" & studen tzongshu den tn u + 1 & " : i" & studen tzongshu ) ,xls " ) .-stu2" > =80 " )w o rkbook s

55、 (w o rkbook s ( "总表 . xls" ) . shee ts( 1 ) . r ange ( " b " & studen tzongshu ) . v a lue & ".70 分到 79 分的学生人数w o rkbook s ( " c jb.xls " ) . shee ts ( 1 ) .w o rk shee tfunc tion. xls " ) . shee ts ( 1 ) .- studen tn u + 1 &xls" ) . c lo se关

56、闭文档r ange" d39 " ) . v a lue=(y = studen tzongshustuden tn u = studen tzongshuse t exlapp = no th ing coun tif (w o rkbook s ( " 总 表 .r ange ( " i" & studen tzongshu清空变量" : i" & studen tzongshu) , " > = 60 " ) - w o rk she2loopend sube tfunc tion. coun t if ( w o rkbook s ( " 总 表 .shee ts ( 1 )