1、Excel.Application) 创建EXCEL对象Set xlBook = xlApp.Workbooks.Open(文件名打开已经存在的EXCEL工件簿文件xlApp.Visible = True 设置EXCEL对象可见(或不可见)Set xlSheet = xlBook.Worksheets(表名设置活动工作表xlSheet.Cells(row, col) =值 给单元格(row,col)赋值xlSheet.PrintOut 打印工作表xlBook.Close (True) 关闭工作簿xlApp.Quit 结束EXCEL对象Set xlApp = Nothing 释放xlApp对象x
2、lBook.RunAutoMacros (xlAutoOpen) 运行EXCEL启动宏xlBook.RunAutoMacros (xlAutoClose) 运行EXCEL关闭宏4、在运用以上VB命令操作EXCEL表时,除非设置EXCEL对象不可见,否则VB程序可继续执行其它操作,也能够关闭EXCEL,同时也可对EXCEL进行操作。但在EXCEL操作过程中关闭EXCEL对象时,VB程序无法知道,如果此时使用EXCEL对象,则VB程序会产生自动化错误。形成VB程序无法完全控制EXCEL的状况,使得VB与EXCEL脱节。二、 EXCEL的宏功能:EXCEL提供一个Visual Basic编辑器,打开
3、Visual Basic编辑器,其中有一工程属性窗口,点击右键菜单的插入模块,则增加一个模块1,在此模块中可以运用Visual Basic语言编写函数和过程并称之为宏。其中,EXCEL有两个自动宏:一个是启动宏(Sub Auto_Open(),另一个是关闭宏(Sub Auto_Close()。它们的特性是:当用EXCEL打含有启动宏的工簿时,就会自动运行启动宏,同理,当关闭含有关闭宏的工作簿时就会自动运行关闭宏。但是通过VB的自动化功能来调用EXCEL工作表时,启动宏和关闭宏不会自动运行,而需要在VB中通过命令xlBook.RunAutoMacros (xlAutoOpen)和xlBook.R
4、unAutoMacros (xlAutoClose) 来运行启动宏和关闭宏。三、 VB与EXCEL的相互勾通:充分利用EXCEL的启动宏和关闭宏,可以实现VB与EXCEL的相互勾通,其方法如下:在EXCEL的启动宏中加入一段程序,其功能是在磁盘中写入一个标志文件,同时在关闭宏中加入一段删除此标志文件的程序。VB程序在执行时通过判断此标志文件存在与否来判断EXCEL是否打开,如果此标志文件存在,表明EXCEL对象正在运行,应该禁止其它程序的运行。如果此标志文件不存在,表明EXCEL对象已被用户关闭,此时如果要使用EXCEL对象运行,必须重新创建EXCEL对象。四、VB控制EXCLE自动打印表格:
5、1、在VB中,建立一个FORM2,界面如下:其中要求输入的参数是原表格的纵列,即A列对应1、B列对应2、A列对应1、C列对应3、D列对应4、E列对应5,依此类推,打印时间是控制每打一张表所须时间,单位为毫秒(ms)。当把值设为0时,对应单元格的内容不变,以便灵活应用,须把要打印的表放到C盘,放到别处须要改变程序。然后在其中输入如下程序:Option Explicit Public uint As Integer 单位名称 Public goods As Integer 设备名称 Public number As Integer 设备编号 Public address As Integer 出厂
6、地址 Public modle As Integer 设备型号 Public reference As Integer 参考 Public result As Integer 检定结果 Public dates As Integer 检定日期 Public death As Integer Public cel As Integer 打印张数 Public time As Integer 打印时间Private Sub Command1_Click() Form2.Hide Form1.ShowEnd SubPrivate Sub Command3_Click() uint = Val(Tex
7、t1.Text) goods = Val(Text2.Text) modle = Val(Text3.Text) address = Val(Text4.Text) number = Val(Text5.Text) reference = Val(Text6.Text) result = Val(Text7.Text) dates = Val(Text8.Text) time = Val(Text9.Text) death = Val(Text10.Text) 有效期至 MsgBox !- 参数修改成功-!Private Sub Form_Initialize() 数据初始化 cel = 1
8、uint = 1 goods = 2 number = 3 address = 4 modle = 5 reference = 6 result = 7 dates = 8 death = 9 time = 2000 打印时间间隔Private Sub Command2_Click()End建立一个FORM1,界面如下:Dim present%Private Sub Command1_Click() 打开EXCLE表格 If Dir(C:excel.bz) = Then Set excel = CreateObject(excel.application) Set workbook = exc
9、el.Workbooks.Open(c:自动打印表格.xls Set sheet = workbook.WorkSheets excel.Visible = True workbook.Application.Run auto_open excel.WorkSheets(2).Activate 设置表2为活动表 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(F
10、orm2.cel, Form2.goods) 产品名称 If Form2.number 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(Form2.cel, Form2.number) If Form2.address 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(Form2.cel, Form2.address) 设备厂址 If Form2.modle 0 Then sheet(2).Cells(5, 2) = sheet(1).Cells(Form2.cel, Form2.modle) If Form2.
11、reference 0 Then sheet(2).Cells(6, 2) = sheet(1).Cells(Form2.cel, Form2.reference) 分度号 If Form2.result 0 Then sheet(2).Cells(7, 2) = sheet(1).Cells(Form2.cel, Form2.result) 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
12、) = sheet(1).Cells(Form2.cel, Form2.death) ElseEXCL已打开! End IfPrivate Sub Command2_Click() 关闭退出表格) auto_close Set excel = Nothing workbook.Close (True) Form1.Hide Form2.ShowPrivate Sub Command3_Click() 暂停打印-请打开要打印的表格-! Timer1.Enabled = False-打印暂停-! & Chr(10) &-已打印 Form2.cel - present - 1 &张-!Private
13、 Sub Command4_Click() 继续打印 Timer1.Interval = Form2.time Timer1.Enabled = TruePrivate Sub Command5_Click() 开始打印 present = 0 Form2.cel = 1Private Sub Command6_Click() 下一张 present = present + 1 0 Then sheet(2).Cells(1, 2) = sheet(1).Cells(present, Form2.uint) 0 Then sheet(2).Cells(2, 2) = sheet(1).Cell
14、s(present, Form2.goods) 0 Then sheet(2).Cells(3, 3) = sheet(1).Cells(present, Form2.number) 0 Then sheet(2).Cells(4, 2) = sheet(1).Cells(present, Form2.address) 0 Then sheet(2).Cells(5, 2) = sheet(1).Cells(present, Form2.modle) 0 Then sheet(2).Cells(6, 2) = sheet(1).Cells(present, Form2.reference) 0
15、 Then sheet(2).Cells(7, 2) = sheet(1).Cells(present, Form2.result) 0 Then sheet(2).Cells(11, 2) = sheet(1).Cells(present, Form2.dates) 0 Then sheet(2).Cells(12, 3) = sheet(1).Cells(present, Form2.death) Private Sub Command7_Click() 上一张 present = present - 1 If present = 0 Then present = 1Private Sub
16、 Command8_Click() 从当钱页打印 Form2.cel = presentPrivate Sub Form_Load()Private Sub Timer1_Timer()Dim a$ a = sheet(1).Cells(Form2.cel, 2) 如果单位名称为“”则打印结束 If a excel.ActiveSheet.PrintOut 打印输出 Form2.cel = Form2.cel + 1-表格已打完-!-共打印4、运行VB程序,输入参数点击确定按钮可完成参数的修改,打印时间是控制每打一张表所须时间,打开EXCEL系统后,VB程序和EXCEL分别属两个不同的应用系统,均可同时进行操作,由于系统加了判断,因此在VB程序中重复点击EXCEL按钮时会提示EXCEL已打开。如果在EXCEL中关闭EXCEL后再点EXCEL按钮,则会重新打开EXCEL。而无论EXCEL打开与否,通过VB程序均可关闭EXCEL。这样就实现了VB与EXCEL的无缝连接。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1