vb控制打印機(jī)自動(dòng)打印表格.doc

此文檔收集于網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系網(wǎng)站刪除VB控制EXCLE自動(dòng)打印表格VB是常用的應(yīng)用軟件開發(fā)工具之一，由于VB的報(bào)表功能有限，而且一但報(bào)表格式發(fā)生變化，就得相應(yīng)修改程序，給應(yīng)用軟件的維護(hù)工作帶來(lái)極大的不便。因此充分利用EXECL的強(qiáng)大報(bào)表功來(lái)實(shí)現(xiàn)報(bào)表功能。但由于VB與EXCEL由于分別屬于不同的應(yīng)用系統(tǒng)，如何把它們有機(jī)地結(jié)合在一起，是一個(gè)值得我們研究的課題。一、 VB讀寫EXCEL表：VB本身提自動(dòng)化功能可以讀寫EXCEL表，其方法如下：1、在工程中引用Microsoft Excel類型庫(kù)：從工程菜單中選擇引用欄；選擇Microsoft Excel 9.0 Object Library（EXCEL2003），然后選擇確定。表示在工程中要引用EXCEL類型庫(kù)。2、在通用對(duì)象的聲明過(guò)程中定義EXCEL對(duì)象：Dim excel As ObjectDim workbook As ObjectDim sheet As Object3、在程序中操作EXCEL表常用命令：Set xlApp = CreateObject(Excel.Application) 創(chuàng)建EXCEL對(duì)象Set xlBook = xlApp.Workbooks.Open(文件名) 打開已經(jīng)存在的EXCEL工件簿文件xlApp.Visible = True 設(shè)置EXCEL對(duì)象可見（或不可見）Set xlSheet = xlBook.Worksheets(表名) 設(shè)置活動(dòng)工作表xlSheet.Cells(row, col) =值 給單元格（row,col）賦值xlSheet.PrintOut 打印工作表xlBook.Close (True) 關(guān)閉工作簿xlApp.Quit 結(jié)束EXCEL對(duì)象Set xlApp = Nothing 釋放xlApp對(duì)象xlBook.RunAutoMacros (xlAutoOpen) 運(yùn)行EXCEL啟動(dòng)宏xlBook.RunAutoMacros (xlAutoClose) 運(yùn)行EXCEL關(guān)閉宏4、在運(yùn)用以上VB命令操作EXCEL表時(shí)，除非設(shè)置EXCEL對(duì)象不可見，否則VB程序可繼續(xù)執(zhí)行其它操作，也能夠關(guān)閉EXCEL，同時(shí)也可對(duì)EXCEL進(jìn)行操作。但在EXCEL操作過(guò)程中關(guān)閉EXCEL對(duì)象時(shí)，VB程序無(wú)法知道，如果此時(shí)使用EXCEL對(duì)象，則VB程序會(huì)產(chǎn)生自動(dòng)化錯(cuò)誤。形成VB程序無(wú)法完全控制EXCEL的狀況，使得VB與EXCEL脫節(jié)。二、 EXCEL的宏功能：EXCEL提供一個(gè)Visual Basic編輯器，打開Visual Basic編輯器，其中有一工程屬性窗口，點(diǎn)擊右鍵菜單的插入模塊，則增加一個(gè)模塊1，在此模塊中可以運(yùn)用Visual Basic語(yǔ)言編寫函數(shù)和過(guò)程并稱之為宏。其中，EXCEL有兩個(gè)自動(dòng)宏：一個(gè)是啟動(dòng)宏（Sub Auto_Open()），另一個(gè)是關(guān)閉宏（Sub Auto_Close()）。它們的特性是：當(dāng)用EXCEL打含有啟動(dòng)宏的工簿時(shí)，就會(huì)自動(dòng)運(yùn)行啟動(dòng)宏，同理，當(dāng)關(guān)閉含有關(guān)閉宏的工作簿時(shí)就會(huì)自動(dòng)運(yùn)行關(guān)閉宏。但是通過(guò)VB的自動(dòng)化功能來(lái)調(diào)用EXCEL工作表時(shí)，啟動(dòng)宏和關(guān)閉宏不會(huì)自動(dòng)運(yùn)行，而需要在VB中通過(guò)命令xlBook.RunAutoMacros (xlAutoOpen)和xlBook.RunAutoMacros (xlAutoClose) 來(lái)運(yùn)行啟動(dòng)宏和關(guān)閉宏。三、 VB與EXCEL的相互勾通：充分利用EXCEL的啟動(dòng)宏和關(guān)閉宏，可以實(shí)現(xiàn)VB與EXCEL的相互勾通，其方法如下：在EXCEL的啟動(dòng)宏中加入一段程序，其功能是在磁盤中寫入一個(gè)標(biāo)志文件，同時(shí)在關(guān)閉宏中加入一段刪除此標(biāo)志文件的程序。VB程序在執(zhí)行時(shí)通過(guò)判斷此標(biāo)志文件存在與否來(lái)判斷EXCEL是否打開，如果此標(biāo)志文件存在，表明EXCEL對(duì)象正在運(yùn)行，應(yīng)該禁止其它程序的運(yùn)行。如果此標(biāo)志文件不存在，表明EXCEL對(duì)象已被用戶關(guān)閉，此時(shí)如果要使用EXCEL對(duì)象運(yùn)行，必須重新創(chuàng)建EXCEL對(duì)象。四、VB控制EXCLE自動(dòng)打印表格：1、在VB中，建立一個(gè)FORM2，界面如下：其中要求輸入的參數(shù)是原表格的縱列，即A列對(duì)應(yīng)1、B列對(duì)應(yīng)2、A列對(duì)應(yīng)1、C列對(duì)應(yīng)3、D列對(duì)應(yīng)4、E列對(duì)應(yīng)5，依此類推，打印時(shí)間是控制每打一張表所須時(shí)間，單位為毫秒（ms）。當(dāng)把值設(shè)為0時(shí)，對(duì)應(yīng)單元格的內(nèi)容不變，以便靈活應(yīng)用，須把要打印的表放到C盤，放到別處須要改變程序。然后在其中輸入如下程序：Option Explicit Public uint As Integer 單位名稱 Public goods As Integer 設(shè)備名稱 Public number As Integer 設(shè)備編號(hào) Public address As Integer 出廠地址 Public modle As Integer 設(shè)備型號(hào) Public reference As Integer 參考 Public result As Integer 檢定結(jié)果 Public dates As Integer 檢定日期 Public death As Integer 檢定日期 Public cel As Integer 打印張數(shù) Public time As Integer 打印時(shí)間Private Sub Command1_Click() Form2.Hide Form1.ShowEnd SubPrivate Sub Command3_Click() uint = Val(Text1.Text) 單位名稱 goods = Val(Text2.Text) 設(shè)備名稱 modle = Val(Text3.Text) 設(shè)備型號(hào) address = Val(Text4.Text) 出廠地址 number = Val(Text5.Text) 設(shè)備編號(hào) reference = Val(Text6.Text) 參考 result = Val(Text7.Text) 檢定結(jié)果 dates = Val(Text8.Text) 檢定日期 time = Val(Text9.Text) 打印時(shí)間 death = Val(Text10.Text) 有效期至 MsgBox ！- 參數(shù)修改成功-！End SubPrivate Sub Form_Initialize() 數(shù)據(jù)初始化 cel = 1 打印張數(shù) uint = 1 單位名稱 goods = 2 設(shè)備名稱 number = 3 設(shè)備編號(hào) address = 4 出廠地址 modle = 5 設(shè)備型號(hào) reference = 6 參考 result = 7 檢定結(jié)果 dates = 8 檢定日期 death = 9 有效期至 time = 2000 打印時(shí)間間隔End SubPrivate Sub Command2_Click()EndEnd Sub建立一個(gè)FORM1，界面如下：然后在其中輸入如下程序：Dim excel As ObjectDim workbook As ObjectDim sheet As ObjectDim present%Private Sub Command1_Click() 打開EXCLE表格 If Dir(C:excel.bz) = Then Set excel = CreateObject(excel.application) Set workbook = excel.Workbooks.Open(c:自動(dòng)打印表格.xls) Set sheet = workbook.WorkSheets excel.Visible = True workbook.Application.Run auto_open excel.WorkSheets(2).Activate 設(shè)置表2為活動(dòng)表 If Form2.uint 0 Then sheet(2).Cells(1, 2) = sheet(1).Cells(Form2.cel, Form2.uint) 單位名稱 If Form2.goods 0 Then sheet(2).Cells(2, 2) = sheet(1).Cells(Form2.cel, Form2.goods) 產(chǎn)品名稱 If Form2.number 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(Form2.cel, Form2.number) 設(shè)備編號(hào) If Form2.address 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(Form2.cel, Form2.address) 設(shè)備廠址 If Form2.modle 0 Then sheet(2).Cells(5, 2) = sheet(1).Cells(Form2.cel, Form2.modle) 設(shè)備型號(hào) If Form2.reference 0 Then sheet(2).Cells(6, 2) = sheet(1).Cells(Form2.cel, Form2.reference) 分度號(hào) If Form2.result 0 Then sheet(2).Cells(7, 2) = sheet(1).Cells(Form2.cel, Form2.result) 檢定結(jié)果 If Form2.dates 0 Then sheet(2).Cells(11, 2) = sheet(1).Cells(Form2.cel, Form2.dates) 檢定日期 If Form2.death 0 Then sheet(2).Cells(12, 3) = sheet(1).Cells(Form2.cel, Form2.death) 有效期至 Else MsgBox EXCL已打開！ End IfEnd SubPrivate Sub Command2_Click() 關(guān)閉退出表格 If Dir(C:excel.bz) Then workbook.Application.Run auto_close Set excel = Nothing workbook.Close (True) End If Form1.Hide Form2.ShowEnd SubPrivate Sub Command3_Click() 暫停打印 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else Timer1.Enabled = False MsgBox ！-打印暫停-！ & Chr(10) & ！-已打印 & Form2.cel - present - 1 & 張-！ End IfEnd SubPrivate Sub Command4_Click() 繼續(xù)打印 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else Timer1.Interval = Form2.time Timer1.Enabled = True End IfEnd SubPrivate Sub Command5_Click() 開始打印 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else present = 0 Form2.cel = 1 Timer1.Interval = Form2.time Timer1.Enabled = True End IfEnd SubPrivate Sub Command6_Click() 下一張 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else present = present + 1 If Form2.uint 0 Then sheet(2).Cells(1, 2) = sheet(1).Cells(present, Form2.uint) 單位名稱 If Form2.goods 0 Then sheet(2).Cells(2, 2) = sheet(1).Cells(present, Form2.goods) 產(chǎn)品名稱 If Form2.number 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(present, Form2.number) 設(shè)備編號(hào) If Form2.address 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(present, Form2.address) 設(shè)備廠址 If Form2.modle 0 Then sheet(2).Cells(5, 2) = sheet(1).Cells(present, Form2.modle) 設(shè)備型號(hào) If Form2.reference 0 Then sheet(2).Cells(6, 2) = sheet(1).Cells(present, Form2.reference) 分度號(hào) If Form2.result 0 Then sheet(2).Cells(7, 2) = sheet(1).Cells(present, Form2.result) 檢定結(jié)果 If Form2.dates 0 Then sheet(2).Cells(11, 2) = sheet(1).Cells(present, Form2.dates) 檢定日期 If Form2.death 0 Then sheet(2).Cells(12, 3) = sheet(1).Cells(present, Form2.death) 有效期至 End IfEnd SubPrivate Sub Command7_Click() 上一張 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else present = present - 1 If present = 0 Then present = 1 If Form2.uint 0 Then sheet(2).Cells(1, 2) = sheet(1).Cells(present, Form2.uint) 單位名稱 If Form2.goods 0 Then sheet(2).Cells(2, 2) = sheet(1).Cells(present, Form2.goods) 產(chǎn)品名稱 If Form2.number 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(present, Form2.number) 設(shè)備編號(hào) If Form2.address 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(present, Form2.address) 設(shè)備廠址 If Form2.modle 0 Then sheet(2).Cells(5, 2) = sheet(1).Cells(present, Form2.modle) 設(shè)備型號(hào) If Form2.reference 0 Then sheet(2).Cells(6, 2) = sheet(1).Cells(present, Form2.reference) 分度號(hào) If Form2.result 0 Then sheet(2).Cells(7, 2) = sheet(1).Cells(present, Form2.result) 檢定結(jié)果 If Form2.dates 0 Then sheet(2).Cells(11, 2) = sheet(1).Cells(present, Form2.dates) 檢定日期 If Form2.death 0 Then sheet(2).Cells(12, 3) = sheet(1).Cells(present, Form2.death) 有效期至 End IfEnd SubPrivate Sub Command8_Click() 從當(dāng)錢頁(yè)打印 If Dir(C:excel.bz) = Then MsgBox ！-請(qǐng)打開要打印的表格-！ Else Form2.cel = present Timer1.Enabled = True End IfEnd SubPrivate Sub Form_Load() present = 0 Timer1.Interval = Form2.time Timer1.Enabled = FalseEnd SubPrivate Sub Timer1_Timer()Dim a$ Timer1.Enabled = False a = sheet(1).Cells(Form2.cel, 2) 如果單位名稱為“”則打印結(jié)束 If a Then If Form2.uint 0 Then sheet(2).Cells(1, 2) = sheet(1).Cells(Form2.cel, Form2.uint) 單位名稱 If Form2.goods 0 Then sheet(2).Cells(2, 2) = sheet(1).Cells(Form2.cel, Form2.goods) 產(chǎn)品名稱 If Form2.number 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(Form2.cel, Form2.number) 設(shè)備編號(hào) If Form2.address 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(Form2.cel, Form2.address) 設(shè)備廠址 If Form2.modle 0 Then sheet(2).Cells(5, 2) = sheet(