Realize the seamless connection between VB and Excel

How Excel realizes the seamless connection between VB and Excel

VB is one of the commonly used software development tools, because the report function of VB is limited, and a report format changes, it is necessary to modify the program, to the application software maintenance work brings great inconvenience. So a lot of programmers have now made full use of EXECL's powerful report work to implement report functionality. However, because VB and Excel belong to different application systems respectively, how to combine them organically is a subject worthy of our study.

One, VB reading and writing Excel table:

VB itself to the automation function can read and write Excel table, the method is as follows:

1. Reference the Microsoft Excel type library in the project:

Select the References column from the Project menu, select Microsoft Excel 9.0 Object Library (EXCEL2000), and then select OK. Represents the Excel type library to be referenced in the project.

2. Define an Excel object in the declaration process of a 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 commands:

Set xlapp = CreateObject ("Excel.Application") ' Create an Excel object
Set xlbook = xlApp.Workbooks.Open ("filename") ' Open an existing Excel workbook file
xlapp.visible = True ' Sets the Excel object to be visible (or invisible)
Set xlsheet = xlbook.worksheets ("table name") ' Set the active worksheet
Xlsheet.cells (row, col) = value ' assigns a cell (Row,col) to a value
Xlsheet.printout ' Print sheet
Xlbook.close (True) ' Closes the workbook
xlApp.Quit ' End Excel object
Set xlapp = Nothing ' Frees Xlapp object
Xlbook.runautomacros (xlAutoOpen) ' Run Excel startup macros
Xlbook.runautomacros (xlautoclose) ' run Excel to close macros

4, in the use of the above VB command Operation Excel table, unless you set the Excel object is not visible, otherwise, VB program can continue to perform other operations, can also be able to close Excel, but also to operate on Excel. However, when you close an Excel object during Excel operations, the VB program does not know that if you use Excel objects at this time, the VB program will produce an automation error. The formation of VB program can not fully control the status of Excel, making VB and Excel disjointed.

Second, Excel's macro function:

Excel provides a Visual Basic Editor, opens the Visual Basic Editor, has a project Properties window, and clicks the "Insert module" on the right menu to add a "Module 1", where you can use visual The basic language writes functions and processes and is called macros. Where Excel has two automatic macros: one is a startup macro (sub Auto_Open ()) and the other is a closed macro (sub Auto_Close ()). Their feature is that when you use Excel to hit a workbook that contains a startup macro, the Startup macro runs automatically, and when you close the workbook that contains the closing macro, the close macro automatically runs. However, when the Excel worksheet is invoked by the automation of VB, startup macros and closing macros do not run automatically, but need to be Xlbook.runautomacros (xlAutoOpen) and Xlbook.runautomacros in VB ( xlAutoClose) to run the startup macro and close the macro.

Third, VB and Excel to communicate with each other:

Make full use of Excel's startup macro and turn off macros, you can realize the mutual communication between VB and Excel, the method is as follows:

In Excel, add a program to the startup macro that writes a flag file to the disk and a program that deletes the flag file in the closing macro. VB program in the execution by determining whether this flag file exists to determine if Excel is open, if this flag file exists, indicating that the Excel object is running, you should prevent other programs from running. If this flag file does not exist, indicating that the Excel object has been closed by the user, you must re-create the Excel object if you want to run with an Excel object.

Four, for example:

1. In VB, create a form, put two command buttons on it, change the Caption property of Command1 to Excel,command2 Caption property to end. Then enter the following program:

Dim xlapp as Excel.Application ' defines an Excel class
Dim Xlbook as Excel.Workbook ' defines the Artifact book class
Dim Xlsheet as Excel.Worksheet ' defines a worksheet class
Private Sub Command1_Click () opens Excel procedure
If Dir ("D:\temp\excel.bz") = "Then" Determines whether Excel is open
Set xlapp = CreateObject ("Excel.Application") ' Create Excel application class
xlapp.visible = True ' Set Excel visible
Set xlbook = XlApp.Workbooks.Open ("D:\temp\bb.xls") Open Excel workbook
Set xlsheet = xlbook.worksheets (1) ' Opens Excel worksheet
Xlsheet. Activate ' Activate worksheet
Xlsheet. Cells (1, 1) = "ABC" To assign values to cell 1 driving column
Xlbook.runautomacros (xlAutoOpen) run a startup macro in Excel
Else
MsgBox ("Excel is open")
End If
End Sub

Private Sub Command2_Click ()
If Dir ("D:\temp\excel.bz") <> "Then" is closed by VB Excel
Xlbook.runautomacros (xlautoclose) ' Perform Excel close macros
Xlbook.close (True) ' Closes an Excel workbook
xlApp.Quit ' Close Excel
End If
Set xlapp = Nothing ' Frees Excel objects
End
End Sub


2. Set up a subdirectory named temp on the D-Packing directory, and create an Excel file named "Bb.xls" in the temp directory.

3, open the Visual Basic Editor in "Bb.xls", click the mouse button in the Project window to select the Insert module, enter the program in the module to save:


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 flag file
End Sub

4, run the VB program, click the Excel button to open the Excel system, open the Excel system, VB program and Excel are two different application systems, can operate at the same time, because the system added judgment, So when you click the Excel button repeatedly in the VB program, Excel is prompted to open it. If you close Excel in Excel and then click the Excel button, you will reopen Excel. Whether Excel opens or not, you can turn Excel off by using VB programs. This realizes the VB and the Excel seamless connection.