VB对EXCEL操作的实例

1、vb对excel操作的实例最近，由于工作关系，我用vb6.0做了一个计算成绩的软件，由于我不会数据库技术，同行对excel应用又比较普遍，所以就用vb6.0操作excel完成了成绩统计的任务。先说说窗体，很简单，只运用了菜单，由此来调用程序代码。截图如下：窗体命名为excel操作，共五个一级菜单。创建表册用来制作所用表格。计算成绩用来算成绩。模拟运算用来测试软件，设置了两个子菜单，一个填随机生成的数据。有了数据就可以计算成绩了，看看效果如何。测试完了就可以清空数据了，清空成绩册中的基础数据以后再计算一次成绩就基本可以使表册恢复原样了。其实，我这是多此一举，回头一想，只需要重新创建所用表册就行了

2、。还画蛇添足了俩菜单：计算器和退出。代码也贴出来共享一下。创建表册：一年级：private sub ynjkb_click()call 建空表(10)call 工作表命名(1)call 成绩册(1)unload excel操作end sub创建表册：二年级：private sub enjkb_click()call 建空表(10)call 工作表命名(2)call 成绩册(2)unload excel操作end sub三至六年级略了吧。创建表册：学校总评：private sub xxzp_click()nj(1) = 一年级: nj(2) = 二年级: nj(3) = 三年级: nj(4) =

3、 四年级: nj(5) = 五年级: nj(6) = 六年级call 建空表(2)建立积分表sheets(1).name = 学校积分sheets(2).name = 积分sheets(学校积分).selectrange(a1:i1).mergea1 = 学校积分统计表: a2 = 学校: a3 = 南村小学: a4 = 东风小学: a5 = 兴中小学: a6 = 尧场小学nj(1) = 一年级: nj(2) = 二年级: nj(3) = 三年级: nj(4) = 四年级: nj(5) = 五年级: nj(6) = 六年级for i = 1 to 6cells(2, i + 1) = nj(i

4、) + 积分nextcells(2, 8) = 均积分: cells(2, 9) = 名次range(a1, i6).selectselection.horizontalalignment = xlcentercall 表格加线(range(a2, i6)call 横排(6, 9)activesheet.pagesetup.orientation = xllandscapeunload excel操作end sub创建表册：上报：private sub shangbao_click()call 建空表(2)sheets(1).name = 中心校sheets(中心校).selectcall 上

5、报表(中心校)sheets(2).name = 普小sheets(普小).selectcall 上报表(普小)unload excel操作end sub计算成绩：一年级：private sub yinianji_click()call 打开工作表call 算成绩(1)kmb(1) = 语文: kmb(2) = 数学: kmb(3) = 英语sheets(kmb(1).selectfor i = 1 to 8 记录一年级语文数据with bj(i).xxmc = cells(2, i + 1).dkjs = cells(3, i + 1).xkrs = cells(4, i + 1).xkzf

6、= cells(5, i + 1).xkjgr = cells(6, i + 1).xkyxr = cells(7, i + 1).xkjf = cells(8, i + 1).bjkm = kmb(1).njxh = 1end withnextsheets(kmb(2).selectfor i = 1 to 8 记录一年级数学数据with bj(i + 8).xxmc = cells(2, i + 1).dkjs = cells(3, i + 1).xkrs = cells(4, i + 1).xkzf = cells(5, i + 1).xkjgr = cells(6, i + 1).xk

7、yxr = cells(7, i + 1).xkjf = cells(8, i + 1).bjkm = kmb(2).njxh = 1end withnextdim hgrs(8) 记录各学校合格人数for i = 1 to 8sheets(xx(i).selectszl = application.worksheetfunction.match(总分, range(a2, f2) 总分所在列即合格人数所在列szh = application.worksheetfunction.match(合格人数, range(cells(2, szl), cells(80, szl) “合格人数”所在行h

8、grs(i) = cells(szh + 2, szl)next向学校总评表过录一年级数据nj(1) = 一年级: nj(2) = 二年级: nj(3) = 三年级: nj(4) = 四年级: nj(5) = 五年级: nj(6) = 六年级workbooks.open filename:=activeworkbook.path & 学校总评.xlsfor i = 1 to 16with sheets(积分)sheets(积分).selecta1 = 年级: a2 = 学科: a3 = 学校: a4 = 人数: a5 = 总分: a6 = 及格人数: a7 = 优秀人数: a8 = 积分.ce

9、lls(1, i + 1) = nj(bj(i).njxh).cells(2, i + 1) = bj(i).bjkm.cells(3, i + 1) = bj(i).xxmc.cells(4, i + 1) = bj(i).xkrs.cells(5, i + 1) = bj(i).xkzf.cells(6, i + 1) = bj(i).xkjgr.cells(7, i + 1) = bj(i).xkyxr.cells(8, i + 1) = bj(i).xkjfend withnextworkbooks.open filename:=activeworkbook.path & 上报.xls

10、with sheets(中心校) 过录中心校成绩.cells(bj(1).njxh * 2 + 2, 3) = bj(1).xkrs + bj(2).xkrs + bj(6).xkrs 语文.cells(bj(1).njxh * 2 + 2, 4) = bj(1).xkzf + bj(2).xkzf + bj(6).xkzf.cells(bj(1).njxh * 2 + 2, 5) = round(.cells(bj(1).njxh * 2 + 2, 4) / .cells(bj(1).njxh * 2 + 2, 3), 2).cells(bj(1).njxh * 2 + 2, 6) = bj

11、(1).xkjgr + bj(2).xkjgr + bj(6).xkjgr.cells(bj(1).njxh * 2 + 2, 7) = bj(1).xkyxr + bj(2).xkyxr + bj(6).xkyxr.cells(bj(1).njxh * 2 + 2, 8) = hgrs(1) + hgrs(2) + hgrs(6).cells(bj(1).njxh * 2 + 3, 3) = bj(9).xkrs + bj(10).xkrs + bj(14).xkrs 数学.cells(bj(1).njxh * 2 + 3, 4) = bj(9).xkzf + bj(10).xkzf + b

12、j(14).xkzf.cells(bj(1).njxh * 2 + 3, 5) = round(.cells(bj(1).njxh * 2 + 3, 4) / .cells(bj(1).njxh * 2 + 3, 3), 2).cells(bj(1).njxh * 2 + 3, 6) = bj(9).xkjgr + bj(10).xkjgr + bj(14).xkjgr.cells(bj(1).njxh * 2 + 3, 7) = bj(9).xkyxr + bj(10).xkyxr + bj(14).xkyxr.cells(bj(1).njxh * 2 + 3, 8) = hgrs(1) +

13、 hgrs(2) + hgrs(6)end withwith sheets(普小) 过录普小成绩.cells(bj(1).njxh * 2 + 2, 3) = bj(3).xkrs + bj(4).xkrs + bj(5).xkrs + bj(7).xkrs + bj(8).xkrs 语文.cells(bj(1).njxh * 2 + 2, 4) = bj(3).xkzf + bj(4).xkzf + bj(5).xkzf + bj(7).xkzf + bj(8).xkzf.cells(bj(1).njxh * 2 + 2, 5) = round(.cells(bj(1).njxh * 2 +

14、 2, 4) / .cells(bj(1).njxh * 2 + 2, 3), 2).cells(bj(1).njxh * 2 + 2, 6) = bj(3).xkjgr + bj(4).xkjgr + bj(5).xkjgr + bj(7).xkjgr + bj(8).xkjgr.cells(bj(1).njxh * 2 + 2, 7) = bj(3).xkyxr + bj(4).xkyxr + bj(5).xkyxr + bj(7).xkyxr + bj(8).xkyxr.cells(bj(1).njxh * 2 + 2, 8) = hgrs(3) + hgrs(4) + hgrs(5)

15、+ hgrs(7) + hgrs(8).cells(bj(1).njxh * 2 + 3, 3) = bj(11).xkrs + bj(12).xkrs + bj(13).xkrs + bj(15).xkrs + bj(16).xkrs 数学.cells(bj(1).njxh * 2 + 3, 4) = bj(11).xkzf + bj(12).xkzf + bj(13).xkzf + bj(15).xkzf + bj(16).xkzf.cells(bj(1).njxh * 2 + 3, 5) = round(.cells(bj(1).njxh * 2 + 3, 4) / .cells(bj(

16、1).njxh * 2 + 3, 3), 2).cells(bj(1).njxh * 2 + 3, 6) = bj(11).xkjgr + bj(12).xkjgr + bj(13).xkjgr + bj(15).xkjgr + bj(15).xkjgr.cells(bj(1).njxh * 2 + 3, 7) = bj(11).xkyxr + bj(12).xkyxr + bj(13).xkyxr + bj(15).xkyxr + bj(16).xkyxr.cells(bj(1).njxh * 2 + 3, 8) = hgrs(3) + hgrs(4) + hgrs(5) + hgrs(7)

17、 + hgrs(8)end withunload excel操作end sub计算成绩：二年级略。计算成绩：三年级：private sub sannianji_click()call 打开工作表call 算成绩(3)kmb(1) = 语文: kmb(2) = 数学: kmb(3) = 英语sheets(kmb(1).selectfor i = 1 to 5 记录三年级语文数据with bj(i).xxmc = cells(2, i + 1).dkjs = cells(3, i + 1).xkrs = cells(4, i + 1).xkzf = cells(5, i + 1).xkjgr =

18、cells(6, i + 1).xkyxr = cells(7, i + 1).xkjf = cells(8, i + 1).bjkm = kmb(1).njxh = 3end withnextsheets(kmb(2).selectfor i = 1 to 5 记录三年级数学数据with bj(i + 5).xxmc = cells(2, i + 1).dkjs = cells(3, i + 1).xkrs = cells(4, i + 1).xkzf = cells(5, i + 1).xkjgr = cells(6, i + 1).xkyxr = cells(7, i + 1).xkjf

19、 = cells(8, i + 1).bjkm = kmb(2).njxh = 3end withnextsheets(kmb(3).selectfor i = 1 to 5 记录三年级英语数据with bj(i + 10).xxmc = cells(2, i + 1).dkjs = cells(3, i + 1).xkrs = cells(4, i + 1).xkzf = cells(5, i + 1).xkjgr = cells(6, i + 1).xkyxr = cells(7, i + 1).xkjf = cells(8, i + 1).bjkm = kmb(3).njxh = 3en

20、d withnextxx(1) = 南村1: xx(2) = 南村2: xx(3) = 兴中: xx(4) = 东风: xx(5) = 尧场dim hgrs(5) 记录各学校合格人数for i = 1 to 5sheets(xx(i).selectszl = application.worksheetfunction.match(总分, range(a2, f2) 总分所在列即合格人数所在列szh = application.worksheetfunction.match(合格人数, range(cells(2, szl), cells(80, szl) “合格人数”所在行hgrs(i) =

21、cells(szh + 2, szl)next向学校总评表过录积分nj(1) = 一年级: nj(2) = 二年级: nj(3) = 三年级: nj(4) = 四年级: nj(5) = 五年级: nj(6) = 六年级workbooks.open filename:=activeworkbook.path & 学校总评.xlsfor i = 1 to 15with sheets(积分)a21 = 年级: a22 = 学科: a23 = 学校: a24 = 人数: a25 = 总分: a26 = 及格人数: a27 = 优秀人数: a28 = 积分.cells(21, i + 1) = nj(b

22、j(i).njxh).cells(22, i + 1) = bj(i).bjkm.cells(23, i + 1) = bj(i).xxmc.cells(24, i + 1) = bj(i).xkrs.cells(25, i + 1) = bj(i).xkzf.cells(26, i + 1) = bj(i).xkjgr.cells(27, i + 1) = bj(i).xkyxr.cells(28, i + 1) = bj(i).xkjfend withnextworkbooks.open filename:=activeworkbook.path & 上报.xlswith sheets(中

23、心校) 过录中心校成绩.cells(bj(1).njxh * 3 - 1, 3) = bj(1).xkrs + bj(2).xkrs 语文.cells(bj(1).njxh * 3 - 1, 4) = bj(1).xkzf + bj(2).xkzf.cells(bj(1).njxh * 3 - 1, 5) = round(.cells(bj(1).njxh * 3 - 1, 4) / .cells(bj(1).njxh * 3 - 1, 3), 2).cells(bj(1).njxh * 3 - 1, 6) = bj(1).xkjgr + bj(2).xkjgr.cells(bj(1).njx

24、h * 3 - 1, 7) = bj(1).xkyxr + bj(2).xkyxr.cells(bj(1).njxh * 3 - 1, 8) = hgrs(1) + hgrs(2).cells(bj(1).njxh * 3, 3) = bj(6).xkrs + bj(7).xkrs 数学.cells(bj(1).njxh * 3, 4) = bj(6).xkzf + bj(7).xkzf.cells(bj(1).njxh * 3, 5) = round(.cells(bj(1).njxh * 3, 4) / .cells(bj(1).njxh * 3, 3), 2).cells(bj(1).n

25、jxh * 3, 6) = bj(6).xkjgr + bj(7).xkjgr.cells(bj(1).njxh * 3, 7) = bj(6).xkyxr + bj(7).xkyxr.cells(bj(1).njxh * 3, 8) = hgrs(1) + hgrs(2).cells(bj(1).njxh * 3 + 1, 3) = bj(11).xkrs + bj(12).xkrs 英语.cells(bj(1).njxh * 3 + 1, 4) = bj(11).xkzf + bj(12).xkzf.cells(bj(1).njxh * 3 + 1, 5) = round(.cells(b

26、j(1).njxh * 3 + 1, 4) / .cells(bj(1).njxh * 3 + 1, 3), 2).cells(bj(1).njxh * 3 + 1, 6) = bj(11).xkjgr + bj(12).xkjgr.cells(bj(1).njxh * 3 + 1, 7) = bj(11).xkyxr + bj(12).xkyxr.cells(bj(1).njxh * 3 + 1, 8) = hgrs(1) + hgrs(2)end withwith sheets(普小) 过录普小成绩.cells(bj(1).njxh * 3 - 1, 3) = bj(3).xkrs + b

27、j(4).xkrs + bj(5).xkrs 语文.cells(bj(1).njxh * 3 - 1, 4) = bj(3).xkzf + bj(4).xkzf + bj(5).xkzf.cells(bj(1).njxh * 3 - 1, 5) = round(.cells(bj(1).njxh * 3 - 1, 4) / .cells(bj(1).njxh * 3 - 1, 3), 2).cells(bj(1).njxh * 3 - 1, 6) = bj(3).xkjgr + bj(4).xkjgr + bj(5).xkjgr.cells(bj(1).njxh * 3 - 1, 7) = b

28、j(3).xkyxr + bj(4).xkyxr + bj(5).xkyxr.cells(bj(1).njxh * 3 - 1, 8) = hgrs(3) + hgrs(4) + hgrs(5).cells(bj(1).njxh * 3, 3) = bj(8).xkrs + bj(9).xkrs + bj(10).xkrs 数学.cells(bj(1).njxh * 3, 4) = bj(8).xkzf + bj(9).xkzf + bj(10).xkzf.cells(bj(1).njxh * 3, 5) = round(.cells(bj(1).njxh * 3, 4) / .cells(b

29、j(1).njxh * 3, 3), 2).cells(bj(1).njxh * 3, 6) = bj(8).xkjgr + bj(9).xkjgr + bj(10).xkjgr.cells(bj(1).njxh * 3, 7) = bj(8).xkyxr + bj(9).xkyxr + bj(10).xkyxr.cells(bj(1).njxh * 3, 8) = hgrs(3) + hgrs(4) + hgrs(5).cells(bj(1).njxh * 3 + 1, 3) = bj(13).xkrs + bj(14).xkrs + bj(15).xkrs 英语.cells(bj(1).n

30、jxh * 3 + 1, 4) = bj(13).xkzf + bj(14).xkzf + bj(15).xkzf.cells(bj(1).njxh * 3 + 1, 5) = round(.cells(bj(1).njxh * 3 + 1, 4) / .cells(bj(1).njxh * 3 + 1, 3), 2).cells(bj(1).njxh * 3 + 1, 6) = bj(13).xkjgr + bj(14).xkjgr + bj(15).xkjgr.cells(bj(1).njxh * 3 + 1, 7) = bj(13).xkyxr + bj(14).xkyxr + bj(1

31、5).xkyxr.cells(bj(1).njxh * 3 + 1, 8) = hgrs(3) + hgrs(4) + hgrs(5)end withunload excel操作end sub计算成绩：三至六年级略。计算成绩：学校总评：private sub 学校总评_click()call 打开工作表for i = 1 to 6call 算学校积分(i)nextsheets(学校积分).selectfor i = 3 to 6range(h & i) = round(application.worksheetfunction.sum(range(range(b & i), range(g & i) / 6, 2)nextfor i = 3 to 6range(i & i) = application.worksheetfunction.rank(range(h & i), range