Reduce font
Increase font size
VB is one of the commonly used application software development tools. Due to the limited report functions of VB, you must modify the report format once the report format changes.Program, Causing 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, since VB and Excel belong to different application systems, how can we combine them organically?
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 Excel. 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. Shape
The vbprogram cannot fully control the Excel status, which disconnects VB from Excel. Ii. Excel macro functions: Excel provides a visual basic editor to open the visual
In the Basic Editor, there is a project Properties window. Right-click the "insert module" in the menu and add "Module 1". In this module, you can use visual
BasiC LanguageWriting functions and processes is called 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 feature is that when you use Excel to create a workbook containing a macro, the macro will be automatically started. Similarly, when you close a workbook containing a macro, The off function will automatically run.
Close the macro. However, when you use the automation function of VB to call an Excel worksheet, starting or disabling macros does not run automatically.
Xlbook. runautomacros (xlautoopen) and xlbook. runautomacros (xlautoclose)
To run the Enable macro and disable the 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 the vbprogram is executed
Determine whether the mark file exists to determine whether the Excel file is opened. If the mark file exists, it indicates that the EXCEL object is running and other programs are prohibited from running. If this flag does not exist
The clear EXCEL object has been disabled 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 |
