How to achieve seamless connection between VB and Excel

From: Wu Gang by yesky

VB is one of the commonly used application software development tools. Due to the limited report functions of VB, once the report format changes, you have to modify the program accordingly, it brings great inconvenience to the maintenance of application software. Therefore, many programmers have now made full use of the powerful report functions of execl to implement the report function. However, because VB and Excel belong to different application systems, how to organically combine them is a topic worthy of our research.

　　I. VB reading and writing Excel tables:

VB provides the automation function to read and write Excel tables. The method is as follows:

1. reference the Microsoft Excel Type Library in the project:

Select "Reference" 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 the declaration of a common object:

Dim xlapp as Excel. Application Dim xlbook as Excel. Workbook Dim xlsheet as Excel. Worksheet

3. Common commands for operating Excel tables in a program:

Set xlapp = Createobject ("Excel. application") 'create an Excel Object Set xlbook = xlapp. workbooks. Open ("file name") 'open an existing Excel Workbook File Xlapp. Visible = true' sets the EXCEL object to be visible (or invisible) Set xlsheet = xlbook. worksheets ("table name") 'sets the activity Worksheet Xlsheet. cells (row, col) = value 'assigns a value to the cell (row, col ). Xlsheet. printout print the worksheet Xlbook. Close (true) 'Close the workbook Xlapp. Quit 'End the Excel Object Set xlapp = nothing 'to release the xlapp object Xlbook. runautomacros (xlautoopen) 'Run the Excel macro Xlbook. runautomacros (xlautoclose) 'Run EXCEL to close the macro

4. When using the preceding VB command to operate an Excel table, the vbprogram can continue to perform other operations, close the Excel file, and perform operations on the Excel file unless the Excel file is invisible. However, when you close an Excel object during an Excel operation, you cannot know the vbprogram. If you use an Excel object at this time, the vbprogram will generate an automation error. The formation of VB program cannot fully control the Excel, making VB and Excel out of touch.

　　Ii. Excel macro functions:

Excel provides a visual basic editor that opens the Visual Basic Editor. In the Project Properties window, right-click the menu and choose "insert module" to add Module 1 ", in this module, you can use the Visual Basic language to compile functions and processes and call them macros. Excel has two automatic macros: one is to start the macro (sub auto_open (), and the other is to close the macro (sub auto_close ()). Their features are: When you use Excel to create a workbook containing a macro, the macro will be automatically started. Similarly, when you close a workbook that contains a disabled macro, the macro is automatically disabled. However, when you use the automation function of VB to call an Excel worksheet, starting or disabling macros does not run automatically. Instead, you need to use the command xlbook in VB. runautomacros (xlautoopen) and xlbook. runautomacros (xlautoclose) to run start macro and close macro.

　　Iii. Interaction between VB and Excel:

Make full use of the Excel macro and close the macro to enable the interaction between VB and Excel. The method is as follows:

Add a program to the startup macro of Excel. The function is to write a flag file to the disk, and add a program to close the macro to delete the flag file. When executing the VB program, you can determine whether the Excel file is opened or not by checking whether the mark file exists. If the mark file exists, it indicates that the EXCEL object is running and other programs should be prohibited from running. If the mark file does not exist, it indicates that the EXCEL object has been closed by the user. To run it with an Excel object, you must recreate the EXCEL object.

　　Iv. Example:

1. Create a form in VB, place two command buttons on it, change the caption attribute of command1 to excel, and change the caption attribute of command2 to end. Then, enter the following program:

Dim xlapp as Excel. Application 'defines the Excel class Dim xlbook as Excel. Workbook 'defines the workpiece book class Dim xlsheet as Excel. worksheet 'defines the worksheet class Private sub commandateclick () 'to open the Excel Process If Dir ("D:/temp/Excel. BZ") = "" then' determine whether Excel is opened Set xlapp = Createobject ("Excel. application") 'create an Excel application class Xlapp. Visible = true' set EXCEL to be visible Set xlbook = xlapp. workbooks. Open ("D:/temp/bb.xls") 'open an Excel Workbook Set xlsheet = xlbook. worksheets (1) 'open an Excel worksheet Xlsheet. Activate activate a worksheet Xlsheet. cells (1, 1) = "ABC" 'assigns a value to the driving column of cell 1. Xlbook. runautomacros (xlautoopen) run the start macro in Excel Else Msgbox ("Excel opened ") End if End sub Private sub command2_click () If Dir ("D:/temp/Excel. BZ") <> "" then "is disabled by VB. Xlbook. runautomacros (xlautoclose) 'execute EXCEL to close the macro Xlbook. Close (true) 'close an Excel Workbook Xlapp. Quit close Excel End if Set xlapp = nothing 'release Excel objects End End sub

2. Create a subdirectory named temp in the root directory of drive D, and create an Excel file named "bb.xls" under the temp directory.

3. Open the Visual Basic Editor in "bb.xls", click the mouse key in the project window to select the insert module, and enter the program storage in the module:

Sub auto_open () Open "D:/temp/Excel. BZ" for output as #1 'write flag File Close #1 End sub Sub auto_close () Kill "D:/temp/Excel. BZ" 'Delete the flag File End sub

4. Run the vbprogram and click the Excel button to open the Excel system. After opening the Excel system, the vbprogram and Excel belong to two different application systems, and can be operated simultaneously. As the system adds a judgment, therefore, when you repeatedly click the Excel button in the VB program, the system prompts that the Excel file has been opened. If you close the Excel file in Excel and click the Excel button again, the Excel file is opened again. Whether Excel is opened or not, Excel can be closed through the VB program. In this way, the seamless connection between VB and Excel is realized.

By referring to the author's description, I operate on WINXP in English. The results are as follows:

First, the control is already Microsoft Excel 11.0 Object Library
Secondly, due to the security settings of Windows System SP2, macro commands are disabled by default. Therefore, the method used to determine whether Excel is running has limitations.

2. Create a subdirectory named temp in the root directory of drive D, and create an Excel file named "bb.xls" under the temp directory.

3. Open the Visual Basic Editor in "bb.xls", click the mouse key in the project window to select the insert module, and enter the program storage in the module:

Sub auto_open () Open "D:/temp/Excel. BZ" for output as #1 'write flag File Close #1 End sub Sub auto_close () Kill "D:/temp/Excel. BZ" 'Delete the flag File End sub

4. Run the vbprogram and click the Excel button to open the Excel system. After opening the Excel system, the vbprogram and Excel belong to two different application systems, and can be operated simultaneously. As the system adds a judgment, therefore, when you repeatedly click the Excel button in the VB program, the system prompts that the Excel file has been opened. If you close the Excel file in Excel and click the Excel button again, the Excel file is opened again. Whether Excel is opened or not, Excel can be closed through the VB program. In this way, the seamless connection between VB and Excel is realized.

By referring to the author's description, I operate on WINXP in English. The results are as follows:

First, the control is already Microsoft Excel 11.0 Object Library
Secondly, due to the security settings of Windows System SP2, macro commands are disabled by default. Therefore, the method used to determine whether Excel is running has limitations.