運用VB調用Excel簡化表格処理

簡介:VB調用Excel簡化表格処理


-VB程序中直接調用Excel的格式如下:
私有子菜單dy _ click (index爲整數)
//這是一個打印菜單[/br Select Case Index
Case 0
On Error GoTo kung
AAA = Shell(" D:/Program Files/Microsoft
Office/Office/Excel。EXE c:/cngl/cngl.xls"，1)
Case 1
on error goto Kong//作爲空表
AAA = Shell(" d:/program files/Microsoft
office/office/EXCEL . EXE c:/cngl/cngly . xls"，1)
Case 3
Case 4
Case 5
End Select
Exit SubEXE C:/cngl/cngly.xls"，1)
END SUB
-因爲Word97、Excel97等高級辦公組件都是這樣的步驟:進入Excel97後，點擊菜單欄中的工具按鈕，選擇宏，然後選擇VB編輯器。
-VB編輯器的編輯界麪與Visual Basic非常相似。我們可以從其控件“項目瀏覽器”中插入所需的“添加用戶表單、添加模塊、添加類模塊”，從“屬性窗口”中添加屬性。
-比如我們可以做一個打印表單，可以定位數據，通過程序循環調用打印過程。
Public pan Duan As Boolean
Private Sub command button 1 _ Click()
change
If pan Duan Then
Unload Me
Else
textbox 1 .text =""
End If
End Sub
Sub change()
If Not judged day(textbox 1。Text)然後轉到error
pan Duan = True
Dim SQL as string
Dim DB as database
Dim RS as recordset
SQL =" select * from data table"
SQL = SQL" where((data table。date) =
#" TextBox1。text" #))"
Setdb = open database(Application。this workbook . Path
"/cngl . MDB")
Set RS = db。OpenRecordset(sql，Open dynaset)
if RS . eof then
msgbox("此日期無數據")exit sub
end if
daima 1 = RS . fields(" code")
sheet 1 . range(" E5。價值=盧比。fields(" date")
sheet 1 . range(" F7")。value = rs.fields("數據表記錄")
sheet1.range ("D13")。value = RS . fields(" integer 100" 1 . range(" d15")。value = RS . fields(" integer 50")
sheet 1 . range(" d17")。value = RS . fields(" integer 10")
sheet 1 . range(" d19")..
sheet1.range ("d21")。value = RS . fields(" integer 2")
sheet 1 . range(" d23")。value = RS . fields(" integer 1")
sheet 1 . range(" H1。價值=盧比。fields(" other 100")
sheet 1 . range(" H15")。value = RS . fields(" other 50")
sheet 1 . range(" H17")。value = RS . fields(" other 17" 1 . range(" H19")。value = RS . fields(" other 5")
sheet 1 . range(" H21")。value = RS . fields(" other 2")
sheet 1 . range(" H23")。value br/]Sheet1。範圍(“d37”)。值= Sheet1。範圍
(“d13”)。值* 100 Sheet1。範圍(“d15”)。
值* 50 Sheet1。範圍(“d17”)。值* 10 [/ br/]Sheet1。範圍(“d19”)。值* 5 Sheet1。範圍
(“d21”)。值* 2 Sheet1。範圍(“d23”)。值
Sheet1。範圍(“h37”)。值= Sheet1。範圍
(“H13”)。值* 100 Sheet1。範圍(“h15”)。
值* 50 Sheet1。範圍(“h17”)。值* 10
Sheet1。範圍(“h19”)。值* 5 Sheet1。範圍
(“h21”)。值* 2 Sheet1。範圍(“h23”)。value
Dim SQL 1 As String
Dim db1 As Database
Dim rs1 As recordset
SQL 1 =" select * from code dictionary"
SQL 1 = SQL 1" where((code dictionary。code)=
" & daima 1 &""
setdb 1 = open database(Application。this workbook . Path
"/cngl . MDB")
Set rs1 = db1。OpenRecordset(sql1，Open dynaset)
sheet 1 . range(" H41")。value = rs1.fields("代碼字典名稱")
exit Sub
error:
msgbox("日期輸入錯誤")
pan]End Sub
Private Sub textbox 1 _ Change()
End Sub
Private Sub UserForm _ Activate()
dyxjkc。Top = 30
dyxjkc。left = 230
End
-具躰的表格繪制可以由Excel自動完成，簡化了VB編程的步驟，節省了調試程序和繪圖定位的時間。

位律師廻複