How to implement a seamless connection of VB and Excel
VB is one of the commonly used application software development tools. Since the VB's reporting function is limited, and the report format changes, it has to modify the program corresponding to the maintenance of the application software. Therefore, many programmers have now taken full use of the powerful reporting of Execl to implement reporting. However, due to VB and Excel, how to combine them organically due to different application systems, it is a topic worthy of our research.
First, VB reads and writes the Excel table:
The VB itself automation function can read and write the Excel table, which is as follows:
1. References in the project Microsoft Excel type library:
From the Project menu, select the "Reference" bar; select Microsoft Excel 9.0 Object Library, then select "OK". Indicates that the Excel type library is to be referenced in the project.
2. Define an Excel object during a declaration of a general object:
DIM XLAPP AS Excel.ApplicationDim Xlbook as Excel.WorkbookDim Xlsheet as Excel.Worksheet
3. Operate the Excel text in the program:
Set xlapp = createObject ("excel.application") 'Create an Excel object set xlbook = xlapp.workbooks.open ("File Name)' Open Excel Workpiece Book File XLapp.Visible = true 'Set the Excel object visible (or Not visible) Set Xlsheet = XLbook.worksheets ("Table Name) 'Settings Activity Worksheet Xlsheet.cells (Row, COL) = Value' to Cell (Row, Col) Assignment XLsheet.Printout 'Print Worksheet XLBook.close (TRUE) 'Close Workbook XLAPP.QUIT' End Excel Object Set XLAPP = Nothing 'Release XLAPP Object XLbook.Runautomacros (XLAUTOOPEN)' Run Excel Start Macro XLbook.Runautomacros (XLAUTOCLOSE) 'Run Excel Close Macro
4. When using the VB command to operate the Excel table, the VB program can continue to perform other operations unless the Excel object is set, or the VB program can also turn off Excel, and the Excel can be operated. However, when the Excel object is turned off during the Excel operation, the VB program cannot know. If you use the Excel object at this time, the VB program generates an automation error. Forming VB procedures cannot fully control the status of Excel, making VBs and Excel.
Second, Excel's macro function:
Excel provides a Visual Basic editor, open the Visual Basic Editor, where there is an engineering property window, click "Insert Module" in the right manu, add a "module 1", in which the Visual Basic language must be written in the function and process It is called a macro. Among them, Excel has two automatic macros: one is start macro (SUB Auto_Open ()), the other is to close the macro (SUB Auto_Close ()). Their characteristics are: When using Excel to hit a start-up macro, it will automatically run the macro, which is the same, when it turns off the workbook containing the closed macro, it will automatically turn off the macro. However, when the Excel worksheet is invoked by VB, the start macro and shutdown macro do not run automatically, and the startup macro and shutdown macro are running in VB through command XLbook.Runautomacros (XLAUTOOPEN) and XLbook.Runautomacros (XLAUTOCLOSE). Third, VB and Excel interaction:
Take advantage of Excel's start macros and close macros, you can implement the mutual communication of VB and Excel, which is as follows:
A program is added to the Excel's launch macro whose function is written to a logo file in the disk while adding a program that deletes this flag file in the closing macro. The VB program is executed by determining whether the Excel is open by judging whether the flag file exists. If this flag file exists, it indicates that the Excel object is running, and the operation of other programs should be prohibited. If this flag file does not exist, it indicates that the Excel object has been shut down by the user. At this time, if you want to use the Excel object to run, you must recreate the Excel object.
Fourth, an example:
1. In VB, create an Form, place two command buttons, change the CAPTION attribute of Command1 to Excel, and Command2 Caption property is changed to END. Then enter the following procedures in it:
DIM XLAPP AS Excel.Application 'Defines Excel Class DIM XLBOOK AS Excel.Workbook' Defining Artifact Book Dim Xlsheet As Excel.Worksheet 'Defines Works Table Class Private Sub Command1_Click ()' Open Excel Process IF DIR ("D: / TEMP / Excel.bz ") =" "" THEN 'Judging whether Excel opens set xlapp = createObject ("excel.application")' Create an Excel application class XLapp.visible = true 'Setting Excel visible set xlbook = xlapp.workbooks.open ("D" : /Temp/bb.xls ") 'Open Excel Workbook Set Xlsheet = XLbook.worksheets (1)' Open Excel Work Wheelings XLsheet.Activate 'Activation Worksheet Xlsheet.cells (1, 1) =" ABC "to 1 travel column assignment xlbook.runautomacros (XLautoopen) Running the start macro else msgbox in Excel ("Excel Opened") End IfEnd Sub
Private Sub Command2_Click () IF DIR ("D: /TEMP /EXCEL.BZ") <> "" "by VB Close Excel XLbook.Runautomacros (XLAUTOCLOSE) 'Perform Excel Close Macro XLbook.close (TRUE)' Close Excel Work Book XLapp.quit 'Close Excel End If Set XLAPP = Nothing' Release Excel Object Endend Sub2, build a subdirectory named TEMP on the D drive root directory, build an Excel named "bb.xls" in the Temp directory file.
3. Open the Visual Basic editor in "bb.xls", point the mouse button in the engineering window to select the insert module, enter the subsection deposit in the module:
Sub auto_open () open "d: /temp/excel.b" for output as # 1 'Written Sign File Close # 1END SUBSUB Auto_Close () Kill "D: /temp/excel.bz" delete flag file End Sub
4, run the VB program, click the Excel button to open the Excel system, after opening the Excel system, VB program and Excel are two different application systems, and can be operated simultaneously. Since the system is added, it is repeated in the VB program. Click the Excel button to prompt Excel to open. If you close the Excel in Excel, you will then open the Excel. Regardless of the Excel open or not, you can close the Excel through the VB program. This enables the seamless connection of VB and Excel.
