How to implement the seamless connection VB of VB and Excel is one of the commonly used application software development tools, because VB's report is limited, and one, the report format changes, it has to modify the program accordingly, to the application software maintenance work Large inconvenience. 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 Excel table: VB itself automation function can read and write Excel tables, the method is as follows: 1. Reference Microsoft Excel type library in the project: Select the "Reference" bar from the Project menu; select Microsoft Excel 9.0 Object Library (Excel2000) and 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 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, using VB When the command operates the Excel table, unless the Excel object is not visible, the VB program can continue to perform other operations, and you can also turn off Excel, and you can operate Excel. 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 Menu, add a "module 1", which can be used in this module Visual Basic language writes functions and processes and is called 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's mutual interruption: Take advantage of Excel's start macro and shut down macro, you can implement the mutual communication of VB and Excel, which is as follows: Add a program in Excel's startup macro, which is written in the disk. The logo file, while joining 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, for example: 1, in VB, establish a form, place two command buttons, change the CAPTION attribute of Command1 to Excel, and the CAPTION property of Command2 is changed to END. Then enter the following procedures: DIM XLAPP AS Excel.Application 'Defines Excel class DIM XLBOOK AS Excel.Workbook' Defining Artifact Book DIM XLSHEET AS Excel.Worksheet 'Defining Works Table Private Sub Command1_Click () Open Excel Process IF DIR ("D: /TEMP /EXCEL.BZ") = "" "" "" "EXCEL.Application") 'creates Excel application class XLAPP.Visible = true' Sets Excel visible set xlbook = xlapp .Workbooks.open ("D: /TEMP/BB.XLS") 'Open Excel Workbook Set Xlsheet = XLbook.Worksheets (1)' Open Excel Worksheet Xlsheet.ActiVate 'Activation Worksheet Xlsheet.cells (1, 1) = "ABC" 'to cell 1 travel column assignment XLbook.Runautomacros (XLAUTOOPEN) running in Excel Else MsgBox ("Excel Open") End iFend SubPrivate Sub Command2_Click () if Dir ("D: / TEMP / EXCEL .bz ") <>" "" by VB Close Excel XLbook.Runautomacros (XLAUTOCLOSE) 'Execution Excel Close Macro XLbook.close (True)' Close Excel Workbook XLapp.quit 'Turn Excel end if set xlapp = Nothing' release EXCEL object Endend Sub 2, establishes a subdirectory named TEMP on the D-plate root directory, established an Excel file called "bb.xls" in the Temp directory. 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" deleted flag file End Sub 4, Run the VB program, click the Excel button to open the Excel system. After opening the Excel system, the VB program and Excel are two different application systems, and they can operate simultaneously. Since the system is judged, repeatedly click Excel in the VB program. This will prompt Excel when the button is turned on. 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.
