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

《利用VBA通过Excel数据交互实现模版的自动填写》由会员分享，可在线阅读，更多相关《利用VBA通过Excel数据交互实现模版的自动填写（6页珍藏版）》请在装配图网上搜索。

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 o rkbook s ( c jb. xls ) . Shee ts

5、( 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 & 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 t

6、s ( 1 ) . R ange ( 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 ( 1 ) . R ange ( i & l + y) . V a lue W o rkbook s ( c jb. xls ) . She

7、e ts ( 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 ( h & l + 9 ) . V a lue = W o rkbook s ( 总 表. xls ) . Shee ts

8、( 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 ( 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 t

9、s ( 1 ) . R ange ( e & l + y) . V a lue W 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 & 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 ( 总 表. xls ) . Shee

10、 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 o rkbook s ( c jb. xls ) . Shee ts ( 1 ) . R ange ( n & l + 9 ) . V a lue = W o rkbook s ( 总 表. xls ) . She

11、e 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 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对象变量 ,

12、然后 再用 Ge tO b jec t ( ) 或者 C rea teO b jec t ( )函数返回一个 Exce l App lica tion 对象 ,打开作 ,如以下代码所示 :打开 Exce l模版Exce l文档进行操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 如果 程外对象没有创建 ,则创建一

13、个Exce l进= C rea teO b jec t ( Exce l. Ap 2Se t exlAppp lica tion )E lseEnd IfE rr. 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 = 80 )Shee ts- W o rk shee tFunc tion.Coun t

14、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. 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

15、 ( i & stu2 den tZongshu - studen tN u + 1 & : i & studen t2c6 ) . V a lue = W o rkbook s ( 总 表 . xls ) .( 1 ) . R ange ( f & studen tZongshu) . V a lue1. 4成绩分析在成绩表上要求填写本班级期末总评的成绩 分析. 如 图 ( 2 ) . 可 以 考 虑 在 模 版 上 利 用 公 式 完 成 ,如图 ( 3 ) . 也可以通过 VBA 编程实现 :Shee tsZongshu ) , = 70 )- W o rk shee tFunc tio

16、n.Coun t If (W o rkbook s ( 总 表 . 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 tFunc tion. Coun t If (W o rk2book s ( 总表. xls ) . Shee ts ( 1 )

17、. 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 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

18、) . R ange ( d40 ) . V a lue = W o rk shee tFunc tion. Coun t If (W o 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 ) . R ange ( i s

19、tuden tN u + 1 & : i & stu2den tZongshu) , = 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 & studen tZongshu : i & studen tZongshu) , 100 )将已经制作完成的成绩表用班级名称另存 ,并关闭本文档W o rkbook s ( c jb. xls ) . SaveA s F ilenam e:

20、= D: M y Docum en ts 2 & W o rkbook s ( 总 表 . xls ) . Shee ts ( 1 ) . R ange ( b & 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第一行为标题栏第一

21、行为标题栏A c tiveShee t. R ange ( 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 & studen tZongshu + 2 ) .关闭文档= No th ingxls ) . C lo s

22、eSe 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 extSe t exlApp = Ge tO b jec t ( , Exce l. App lica2获得一个

23、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 打开 Exce l文档2 c jb. xlsexlApp. W indow s. V isib le = True 设为可见判断填写成绩所需要的列数If studen tN u 25 Then y

24、= 25 + yFo r l = 1 To studen tN uCoun t If (W o rkbook s ( 总 表 .R ange ( 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 & : i & studen tZongshu ) ,

25、 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 rkbook s ( c jb. xls ) . Shee ts ( 1 ) . R ange ( i & l + 9 ) . V a lue = W o rkbook s ( 总 表 . xls ) . Shee ts ( 1 ) .

26、R ange ( d & l + y) . V a lue W 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 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 )

27、 . R ange ( g & 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 ange ( m & l + 9 ) . V a lue = W o rkbook s ( 总 表 . xls ) . Shee ts (

28、 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 ) . 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 a

29、nge d37 ) . V a lue=(Coun t If (W o rkbook s ( 总 表 .R ange ( i & 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分的学生人数xls ) .-stu2 =W o rkbook

30、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 & studen tZongshu) , = 70 ) - W o rk she2e tFunc tion. Coun t If ( W o rkbook s ( 总 表 .Shee ts ( 1 ) . R ange ( i

31、& studen tZongshu den tN u + 1 & : i & studen tZongshu ) ,xls ) .-stu2 =80 )W o rkbook s (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

32、&xls ) . C lo se关闭文档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 ) . R a

33、nge ( i & studen tZongshu den tN u + 1 & : i & studen tZongshu ) ,70 )60 分到 69 分的学生人数xls ) .-stu24 结束语通过 VBA 语言 ,利用 Exce l文档对象属性 ,完 成了 Exce l工作表之间的数据交互 , 如图 ( 6 ) . 达 到了工作 的 目 的 , 降 低 了 工 作 的 强 度. 本 程 序 在 XP + O ffice 2003的工作环境下通过. 在工作界面 上还可以进一步优化 ,加强人机对话功能 ,使操作 更亲切 ,工作更轻松.=W o rkbook s ( c jb.xls )

34、 .Shee ts ( 1 ) .R ange d40 ) . V a lue=(W o rk shee tFunc tion.xls ) . Shee ts ( 1 ) .- studen tN u + 1 &Coun tIf (W o rkbook s ( 总 表 .R ange ( i & studen tZongshu : i & studen tZongshu ) , 60 ) - W o rk shee t2Func tion. Coun t If (W o rkbook s ( 总表. xls ) . Shee ts( 1 ) . R ange ( i & studen tZon

35、gshu - studen tN u +1 & : i & studen tZongshu) , 0 )不及格的学生人数W o rkbook s ( c jb. xls ) . Shee ts ( 1 ) .R ange ( c41 ) . V a lue = W o rk shee tFunc tion. A ve r2 age (W o rkbook s ( 总表. xls ) . Shee ts ( 1 ) . R ange ( i & studen tZongshu - studen tN u + 1 & : i &studen tZongshu) )班级均分W o rkbook s

36、c jb.xls ) .SaveA sF ilenam e: = D: M y Docum en ts 2 & W o rkbook s(图 6( 总表. xls ) . Shee ts ( 1 ) . R ange ( b & studen t2Zongshu) . V a lue另存已完成的成绩表参考文献 1 桂俊煜 . Exce l VBA 会计电算化设计基础与实践 M .北京 :机械工业出版社 , 2007. 1 - 2.Tem pla tes A u tom a t ic C om p le t ion through Exce l D a ta w ith VBAMAO Zhan

37、- jun( F re shm an Educa tion D ep a rtm en t of YangtZe U n ive rsity, J ingzhou 434023 , Ch ina)A b stra c t: C la ss Ke su lt tab le s can be comp le ted in office system and VBA au tom tic d ivision da ta au to m a tica lly w ith the Exce l .Key word s:VBA; Exce l; D a ta In te rac tion责任编辑 : D