vb操作excel表的常用方法（Common methods for VB operation of Excel tables）

vb操作excel表的常用方法（Common methods for VB operation of Excel tables） vb操作excel表的常用方法(Common methods for VB operation of Excel tables) Common methods for VB operation of EXCEL tables VB is one of the most popular application software development tools. Due to the limited function of VB reports and the change of the format of reports, the program must be modified accordingly, which brings great inconvenience to the maintenance of application software. As a result, many programmers have now taken full advantage of EXECL's powerful reporting capabilities to implement reporting capabilities. However, since VB and EXCEL belong to different application systems, how to combine them organically is a topic worthy of our study. I. VB reads and writes EXCEL tables: VB itself provides automated capabilities to read and write EXCEL tables, as follows: 1. Refer to the Microsoft Excel type library in the project: From the Project menu, select the reference column, select Microsoft Excel 9, Object Library (EXCEL2000), and then select ok". Indicates that the EXCEL type library is referenced in the project. 2, define the EXCEL object in the declaration of the generic object: Dim xlApp As Excel.Application Dim xlBook As Excel.WorkBook Dim xlSheet As Excel.Worksheet 3, in the program operation EXCEL table, commonly used command: Set xlApp = CreateObject ('Excel.Application') 'creates a EXCEL object Set xlBook = xlApp.Workbooks.Open (file name) 'opens the existing EXCEL job book file XlApp.Visible = True 'sets the EXCEL object visible (or not visible) Set xlSheet = xlBook.Worksheets (table name) 'sets active worksheet XlApp.Cells (row, Col) = 'value' is assigned to the cell (row, Col) XLApp.Cells (I, 1).Interior.ColorIndex = I'design cell color i=1--56 XlSheet.PrintOut 'print worksheet XlBook.Close (True) 'closes Workbook XlApp.Quit 'end the EXCEL object Set xlApp = Nothing 'xlApp object is released XlBook.RunAutoMacros (xlAutoOpen) 'runs EXCEL to start macro XlBook.RunAutoMacros (xlAutoClose) 'runs EXCEL and closes macro You can use the code to get the color list Sub, yansecode () For I = 1, To 56 XlApp.Cells (I, 1) = I Xlapp.Cells (I, 1).Interior.ColorIndex = I Next End Sub 4, when using the above VB command to operate the EXCEL table, unless the EXCEL object is not visible, the VB program can continue to perform other operations, and also can turn off the EXCEL, and also operate on the EXCEL. However, when the EXCEL object is closed during the EXCEL operation, the VB program does not know that if the EXCEL object is used at this point, the VB program generates an automatic error. The VB program can not fully control the EXCEL status, making VB out of line with EXCEL. Two, EXCEL macro function: EXCEL provides a Visual Basic editor, open the Visual Basic editor, one of the project properties window, click "insert" menu, add a "1 module", can be in this module using Visual Basic language function and process called macro. Among them, EXCEL has two automatic macros: one is the start macro (Sub, Auto_Open ()), and the other is the macro Sub (Auto_Close). Their feature is that when you run a workbook containing the start macro with EXCEL, you start the macro automatically. Similarly, when you close a workbook that contains a closed macro, you run the closed macro automatically. But the automation function of VB to call the EXCEL worksheet, starting and closing macro macro will not run automatically, and the need for VB by the xlBook.RunAutoMacros command (xlAutoOpen) and xlBook.RunAutoMacros (xlAutoClose) to start and close the macro macro. Communicate with each other three, VB and EXCEL: Make full use of EXCEL macros and macros start off, communicate with each other can be achieved VB and EXCEL, the method is as follows: Add a program to the startup macro of EXCEL, which has the function of writing a flag file on disk, and adding a program to delete the flag file in the closing macro. The VB program determines whether or not the EXCEL is open by determining whether or not the flag file exists during execution. If the flag file exists and indicates that the EXCEL object is running, other programs should be banned. If this flag file does not exist, indicating that the EXCEL object has been closed by the user, you must recreate the EXCEL object if you want to run the EXCEL object. Four, for example: 1, in VB, establish a FORM, put two command buttons on it, change the Caption attribute of Command1 to EXCEL, and the Caption attribute of Command2 is changed to End. Then enter the following program in it: Dim xlApp As Excel. 应用Excel类定义 昏暗的xlbook Excel工作簿的定义工件簿类。 Excel工作表的昏暗的xlsheet定义工作表类。 私有子command1_click()”打开Excel过程 如果Dir(“D: \温度\ Excel。BZ”)=“”然后“判断Excel是否 打开 集xlapp = CreateObject(“Excel应用”)的创建Excel应用类 xlapp。可见=真正的设置Excel可见 集xlbook = xlapp。工作簿。打开(“D: \温度\ BB .xls”)的打 开Excel工作簿 集xlsheet = xlbook。工作表(1)的打开Excel工作表 xlapp激活工作表激活的。 xlapp。细胞(1, 1)=“ABC”给单元格1行驶列赋值 xlbook.runautomacros(xlautoopen)的运行Excel中的启动宏 其他的 MsgBox(“Excel已打开”) 最后如果 端子 私有子command2_click() 如果Dir(“D: \温度\ Excel。BZ”)< >”然后“由VB关闭Excel xlbook.runautomacros(xlautoclose)的执行Excel关闭宏 xlbook。关闭(真)的关闭Excel工作簿 xlapp退出的关闭Excel。 最后如果 集xlapp =没有释放Excel对象 终点 端子 2、在D盘根目录上建立一个名为温度的子目录，在温度目录下建立一个名为”BB .xls”的Excel文件。 3、在”BB .xls”中打开Visual Basic编辑器，在工程窗口中点鼠标键选择插入模块，在模块中输入入下程序存盘: 子auto_open() 打开“D: \温度\ Excel。BZ”输出为1写标志文件# 近1 # 端子 子auto_close() 杀死“D: \温度\ Excel。BZ”删除标志文件 端子 4、运行VB程序，点击Excel按钮可以打开Excel系统，打开Excel系统后，VB程序和Excel分别属两个不同的应用系统，均可同时进行操作，由于系统加了判断，因此在VB程序中重复点击Excel按钮时会提示Excel已打开。如果在Excel中关闭Excel后再点Excel按钮，则会重新打开Excel。而无论Excel打开与否，通过VB程序均可关闭Excel。这样就实现了VB与Excel的无缝连接。 范例一 明确选择 私有子command1_click() 昏暗的xlapp应用Excel。 昏暗的xlbook Excel工作簿。 昏暗的xlchar作为新的Excel图表 新的Excel工作表xlsheet暗淡。 “建立需要的对象 集xlapp =新的Excel应用。 集xlapp = CreateObject(“Excel应用程序”) 集xlbook = xlapp。工作簿。打开(“F:\ 1 .xls”) 集xlsheet = xlbook工作表(1)。 “建立图对象 集xlchar = xlapp。图。() xlchar.charttype = xllinemarkers 电话xlchar。setsourcedata(xlsheet。范围(“A1:E7”)，xlcolumns) 的原数据 activechart。地点:= xllocationasobject，名称:=“Sheet1” activechart.haslegend =真 activechart.legend.select 选择位置= xlright。 “选中建立的chartobjects对象 在当前工作表(“ChartObjects图表1激活”)。 activechart.plotarea.select activechart.legend.select “删除退出(和X)菜单 模糊i为整数 我= 1 xlapp。CommandBars(“文件”)。controls.count 调试。打印xlapp。CommandBars(“文件”)，对照组(I)。标题 如果留下$(xlapp。CommandBars(“文件”)，对照组(I)。标题，2)=“关闭”然后 xlapp。CommandBars(“文件”)，对照组(I)。可见= false”删除 退出 最后如果 下一个 “显示Excel xlapp可视=真。 端子 范例二 控件 昏暗的xlapp应用Excel。 集xlapp =新的Excel应用。 ----然后，可以用这些变量来访问在应用程序中的从属对象、以及这 些对象的属性和方法例如Excel: 集xlapp = CreateObject(“Excel应用程序”) “激活Excel应用程序 xlapp. visible = false '隐藏excel应用程序窗口 set xlbook = xlapp.workbooks.open (strdestination) "打开工作簿, strdestination为一个excel 报表 企业所得税申报表下载财务会计报表下载斯维尔报表下载外贸周报表下载关联申报表下载 文件 set xlsheet = xlbook.worksheets (1) "设定工作表 vb借助excel实现表格设计 1、定义excel对象变量 dim x as excel.application 2、vb中, 启动excel set x = createobject (excel.application) 3、新建一个excel工作簿 x.workbooks.add 4、选定工作表1 set xsheet = xbook.worksheets (1) 5、指定单元格, 并赋值 ret.fields xsheet.cell (1,1) = (0) 6、指定excel列宽 (1) xsheet.column.columnwidth = 10 7、range 函数, 选定单元格区域 () (in (1.1), cells (10, 20). 8、设置excel字体 xsheet.range (cells (1.1), cells (10,20). font.name = "黑体" 9、设置excel为粗体 xsheet.range (cells (1.1), cells (10, 20).fontbold = true 10、设置excel表格边框 xsheet.range (cells (1.1), cells (10,20). borders.linestyle = xlcontinuous 11、运行时, 显示excel或隐藏excel x.visible = true 12、保存工作簿 xbook save 13、退出excel x.quit 14、交还控制权给excel set x = nothing 15、打印excel表 x.active.window.selectedsheets.printout numcopies copies. 16、退出excel时, 不出现保存提示. x.displayalerts = false 17、怎样运用excel公式? x.range ("a3").formula = = max (a1, a2). 18、excel代码获取技巧 打开excel中的工具 - - - - - - - - - - - - - - - - 宏 录制新宏, 然后进行手工操作, 结束后把所录的宏代码贴进你的vb程序就行. 19、合并单元格代码 x.range (a2, a5).mergecells = true 20、自动调整列宽代码 x.column (":").autofit 'a到 i列自动调整列宽 21、文本自动换行代码 (3) x.rows.wraptext = true 22、设置字体颜色 x.rows font.colorindex = 4 (1). 23、打印预览代码 x.worksheets.printpreview 24、打开excel文件 x.workbooks.open filename = "c: \ my documents and my vb lgx \ dy.xls."