Vb對excel操作的實例

最近，由於工作關係，我用 vb6.0 做了一個計算成績的軟件，由於我不會數據庫技術，同行對excel 應用又比較普遍，所以就用 vb6.0 操作 excel 完成了成績統計的任務。

操作方法

（01）先説説窗體，很簡單，只運用了菜單，由此來調用程序代碼。窗體命名為 excel 操作，共五個一級菜單。

（02）創建表冊用來製作所用表格。

（03）計算成績用來算成績。

（04）模擬運算用來測試軟件，設置了兩個子菜單，一個填隨機生成的數據。有了數據就可以計算成績了，看看效果如何。

（05）測試完了就可以清空數據了，清空成績冊中的基礎數據以後再計算一次成績就基本可以使表冊恢復原樣了。其實，我這是多此一舉，回頭一想，只需要重新創建所用表冊就行了。還畫蛇添足了倆菜單：計算器和退出。

（06）代碼也貼出來共享一下。計算成績：一年級：Private Sub yinianji_Click()Call 打開工作表Call 算成績 (1)kmb(1) = " 語文 ": kmb(2) = " 數學 ": kmb(3) = " 英語 "Sheets(kmb(1))ctFor i = 1 To 8   ' 記錄一年級語文數據With bj(i) = Cells(2, i + 1) = Cells(3, i + 1) = Cells(4, i + 1) = Cells(5, i + 1)r = Cells(6, i + 1)r = Cells(7, i + 1) = Cells(8, i + 1) = kmb(1) = 1End WithNextSheets(kmb(2))ctFor i = 1 To 8   ' 記錄一年級數學數據With bj(i + 8) = Cells(2, i + 1) = Cells(3, i + 1) = Cells(4, i + 1) = Cells(5, i + 1)r = Cells(6, i + 1)r = Cells(7, i + 1) = Cells(8, i + 1) = kmb(2) = 1End WithNextDim hgrs(8)   ' 記錄各學校合格人數For i = 1 To 8Sheets(xx(i))ctszl = h(" 總分 ", Range([a2], [f2])) ' 總分所在列即合格人數所在列szh = h(" 合格人數 ", Range(Cells(2, szl), Cells(80, szl))) '“合格人數”所在行hgrs(i) = Cells(szh + 2, szl)Next' 向學校總評表過錄一年級數據nj(1) = " 一年級 ": nj(2) = " 二年級 ": nj(3) = " 三年級 ": nj(4) = " 四年級 ": nj(5) = " 五年級": nj(6) = " 六年級 " FileName:= & " 學校總評 "For i = 1 To 16With Sheets(" 積分 ")Sheets(" 積分 ")ct[a1] = " 年級 ": [a2] = " 學科 ": [a3] = " 學校 ": [a4] = " 人數 ": [a5] = " 總分 ": [a6] = " 及格人數 ": [a7] = " 優秀人數 ": [a8] = " 積分 "s(1, i + 1) = nj(bj(i))s(2, i + 1) = bj(i)s(3, i + 1) = bj(i)s(4, i + 1) = bj(i)s(5, i + 1) = bj(i)s(6, i + 1) = bj(i)s(7, i + 1) = bj(i)s(8, i + 1) = bj(i)End FileName:= & " 上報 "With Sheets(" 中心校 ") ' 過錄中心校成績s(bj(1) * 2 + 2, 3) = bj(1) + bj(2) + bj(6)  ' 語文s(bj(1) * 2 + 2, 4) = bj(1) + bj(2) + bj(6)s(bj(1) * 2 + 2, 5) = Round(s(bj(1) * 2 + 2, 4) / s(bj(1) * 2 + 2, 3), 2)s(bj(1) * 2 + 2, 6) = bj(1)r + bj(2)r + bj(6)s(bj(1) * 2 + 2, 7) = bj(1)r + bj(2)r + bj(6)s(bj(1) * 2 + 2, 8) = hgrs(1) + hgrs(2) + hgrs(6)s(bj(1) * 2 + 3, 3) = bj(9) + bj(10) + bj(14)  ' 數學s(bj(1) * 2 + 3, 4) = bj(9) + bj(10) + bj(14)s(bj(1) * 2 + 3, 5) = Round(s(bj(1) * 2 + 3, 4) / s(bj(1) * 2 + 3, 3), 2)s(bj(1) * 2 + 3, 6) = bj(9)r + bj(10)r + bj(14)s(bj(1) * 2 + 3, 7) = bj(9)r + bj(10)r + bj(14)s(bj(1) * 2 + 3, 8) = hgrs(1) + hgrs(2) + hgrs(6)End WithWith Sheets(" 普小 ") ' 過錄普小成績s(bj(1) * 2 + 2, 3) = bj(3) + bj(4) + bj(5) + bj(7) + bj(8) ' 語文s(bj(1) * 2 + 2, 4) = bj(3) + bj(4) + bj(5) + bj(7) + bj(8)s(bj(1) * 2 + 2, 5) = Round(s(bj(1) * 2 + 2, 4) / s(bj(1) * 2 + 2, 3), 2)s(bj(1) * 2 + 2, 6) = bj(3)r + bj(4)r + bj(5)r + bj(7)r + bj(8)s(bj(1) * 2 + 2, 7) = bj(3)r + bj(4)r + bj(5)r + bj(7)r + bj(8)s(bj(1) * 2 + 2, 8) = hgrs(3) + hgrs(4) + hgrs(5) + hgrs(7) + hgrs(8)s(bj(1) * 2 + 3, 3) = bj(11) + bj(12) + bj(13) + bj(15) + bj(16) '數學s(bj(1) * 2 + 3, 4) = bj(11) + bj(12) + bj(13) + bj(15) + bj(16)s(bj(1) * 2 + 3, 5) = Round(s(bj(1) * 2 + 3, 4) / s(bj(1) * 2 + 3, 3), 2)s(bj(1) * 2 + 3, 6) = bj(11)r + bj(12)r + bj(13)r + bj(15)r + bj(15)s(bj(1) * 2 + 3, 7) = bj(11)r + bj(12)r + bj(13)r + bj(15)r + bj(16)s(bj(1) * 2 + 3, 8) = hgrs(3) + hgrs(4) + hgrs(5) + hgrs(7) + hgrs(8)End WithUnload excel 操作End Sub

（07）框架已經完成，可以看出，卸載窗體的動作很頻繁。這是我調試的結果，這樣才能保證每次的動作都能順利完成。我想這就是單窗體的特點吧。被調用的程序模塊也挺多的，系統包也調用了，最頻繁的就是打開文件對話框的調用。

（08）哦，還得説明一下，對 excel 的操作前提：Dim xlApp As icationDim xlBook As bookDim xlSheet As sheet本軟件中，我第一次用了自定義類的數據類型。Private Type banjixxmc As Stringbjkm As Stringnjxh As Integerxkrs As Integerdkjs As Stringxkzf As Integerxkjgr As Integerxkyxr As Integerxkjf As LongEnd Type並設了一數組 Dim bj(16) As banji 來從年級成績冊向學校總評表過錄數據。設了八個學校名稱 Dim xx(8) ，設了 3 個科目表 Dim kmb(1 To 3) As String ，六個年級 Dim nj(1 To 6) As String ，班級人數為六個年級八個學校的二維數組 Dim gbrs(6, 8) 。

特別提示

由於篇幅限制，有些程序沒有發出來。