Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of EXCEL operation in VB.NET. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

EXCEL macro features: EXCEL provides a Visual Basic editor, open Visual Basic editor, in which there is a project properties window, right-click the menu "insert module", then add a "module 1", in this module can be written in Visual Basic language functions and procedures and called macros. Among them, 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 the startup macro runs automatically when you use EXCEL to type the workbook that contains the startup macro, and the closing macro runs automatically when the workbook containing the closed macro is closed. But when you invoke the EXCEL worksheet through VB's automation feature, starting and closing macros do not run automatically. Instead, you need to run startup macros and close macros in VB through the commands xlBook.RunAutoMacros (xlAutoOpen) and xlBook.RunAutoMacros (xlAutoClose).

VB.NET EXCEL operation analysis:

By making full use of the startup macro and closed macro of EXCEL, you can realize the mutual communication between VB and EXCEL. The methods are as follows:

Add a program to the startup macro of EXCEL, whose function is to write a flag file on disk and add a program to delete the flag file in the closing macro. The VB program judges whether the EXCEL is open by judging whether the flag file exists or not. if the flag file exists, it indicates that the EXCEL object is running and should prohibit other programs from running. If this flag file does not exist, the EXCEL object has been closed by the user, and if you want to run using the EXCEL object, you must recreate the EXCEL object.

Case study of VB.NET EXCEL:

1. In VB, create a FORM, place two command buttons on it, and change the Caption property of Command1 to the Caption property of EXCEL,Command2 to End. Then enter the following program into it:

Dim xlApp As Excel.Application 'define EXCEL class Dim xlBook As Excel.Workbook' define workbook class Dim xlsheet As Excel.Worksheet 'define worksheet class Private Sub Command1_Click ()' Open EXCEL procedure If Dir ("D:\ temp\ excel.bz") = "" Then 'determine 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)' Open EXCEL worksheet xlsheet.Activate 'activate worksheet xlsheet.Cells (1) 1) = "abc" 'assign a value to cell 1 driving column xlBook.RunAutoMacros (xlAutoOpen) run the startup macro Else MsgBox in EXCEL ("EXCEL is open") End If End Sub Private Sub Command2_Click () If Dir ("D:\ temp\ excel.bz") "Then' close EXCEL xlBook.RunAutoMacros (xlAutoClose) 'execute EXCEL close macro xlBook.Close (True)' close EXCEL workbook xlApp.Quit 'close EXCEL End If Set xlApp = Nothing 'release EXCEL object End End Sub

2. Create a subdirectory named Temp in the root directory of D disk and an EXCEL file named "bb.xls" in the Temp directory.

3. Open the Visual Basic editor in "bb.xls", select the insert module by clicking the mouse button in the project window, and enter the following program to save the disk 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 flag file End Sub

4. Run the VB program and click the EXCEL button to open the EXCEL system. After opening the EXCEL system, the VB program and EXCEL respectively belong to two different application systems and can be operated at the same time. Because of the judgment added by the system, when you click the EXCEL button repeatedly in the VB program, you will be prompted that EXCEL has been opened. If you close EXCEL in EXCEL and then click the EXCEL button, EXCEL will be reopened. Regardless of whether EXCEL is open or not, EXCEL can be closed through the VB program. In this way, the seamless connection between VB and EXCEL is realized.

This is the end of the article on "sample analysis of EXCEL operations in VB.NET". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.