VB對EXCEL操作的實例

1、vb對excel操作的實例最近，由于工作關系，我用vb6.0做了一個計算成績的軟件，由于我不會數(shù)據(jù)庫技術，同行對excel應用又比較普遍，所以就用vb6.0操作excel完成了成績統(tǒng)計的任務。先說說窗體，很簡單，只運用了菜單，由此來調(diào)用程序代碼。截圖如下：窗體命名為excel操作，共五個一級菜單。創(chuàng)建表冊用來制作所用表格。計算成績用來算成績。模擬運算用來測試軟件，設置了兩個子菜單，一個填隨機生成的數(shù)據(jù)。有了數(shù)據(jù)就可以計算成績了，看看效果如何。測試完了就可以清空數(shù)據(jù)了，清空成績冊中的基礎數(shù)據(jù)以后再計算一次成績就基本可以使表冊恢復原樣了。其實，我這是多此一舉，回頭一想，只需要重新創(chuàng)建所用表冊就行了

2、。還畫蛇添足了倆菜單：計算器和退出。代碼也貼出來共享一下。創(chuàng)建表冊：一年級：private sub ynjkb_click()call 建空表(10)call 工作表命名(1)call 成績冊(1)unload excel操作end sub創(chuàng)建表冊：二年級：private sub enjkb_click()call 建空表(10)call 工作表命名(2)call 成績冊(2)unload excel操作end sub三至六年級略了吧。創(chuàng)建表冊：學?？傇u：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 = 學校積分統(tǒng)計表: 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創(chuàng)建表冊：上報：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) = 數(shù)學: kmb(3) = 英語sheets(kmb(1).selectfor i = 1 to 8 記錄一年級語文數(shù)據(jù)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 記錄一年級數(shù)學數(shù)據(jù)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) 記錄各學校合格人數(shù)for i = 1 to 8sheets(xx(i).selectszl = application.worksheetfunction.match(總分, range(a2, f2) 總分所在列即合格人數(shù)所在列szh = application.worksheetfunction.match(合格人數(shù), range(cells(2, szl), cells(80, szl) “合格人數(shù)”所在行h

8、grs(i) = cells(szh + 2, szl)next向學校總評表過錄一年級數(shù)據(jù)nj(1) = 一年級: nj(2) = 二年級: nj(3) = 三年級: nj(4) = 四年級: nj(5) = 五年級: nj(6) = 六年級workbooks.open filename:=activeworkbook.path & 學?？傇u.xlsfor i = 1 to 16with sheets(積分)sheets(積分).selecta1 = 年級: a2 = 學科: a3 = 學校: a4 = 人數(shù): a5 = 總分: a6 = 及格人數(shù): a7 = 優(yōu)秀人數(shù): 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 數(shù)學.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 數(shù)學.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) = 數(shù)學: kmb(3) = 英語sheets(kmb(1).selectfor i = 1 to 5 記錄三年級語文數(shù)據(jù)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 記錄三年級數(shù)學數(shù)據(jù)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 記錄三年級英語數(shù)據(jù)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) 記錄各學校合格人數(shù)for i = 1 to 5sheets(xx(i).selectszl = application.worksheetfunction.match(總分, range(a2, f2) 總分所在列即合格人數(shù)所在列szh = application.worksheetfunction.match(合格人數(shù), range(cells(2, szl), cells(80, szl) “合格人數(shù)”所在行hgrs(i) =

21、cells(szh + 2, szl)next向學?？傇u表過錄積分nj(1) = 一年級: nj(2) = 二年級: nj(3) = 三年級: nj(4) = 四年級: nj(5) = 五年級: nj(6) = 六年級workbooks.open filename:=activeworkbook.path & 學?？傇u.xlsfor i = 1 to 15with sheets(積分)a21 = 年級: a22 = 學科: a23 = 學校: a24 = 人數(shù): a25 = 總分: a26 = 及格人數(shù): a27 = 優(yōu)秀人數(shù): 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 數(shù)學.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 數(shù)學.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計算成績：三至六年級略。計算成績：學?？傇u：private sub 學?？傇u_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