How does ADO connect to an Excel table and access data?

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.

 

Example of opening an Excel file using ADO

Dim conn as new ADODB. Connection
Dim RS as new ADODB. recordset
Dim I as integer
Dim SQL as string

Private sub form_load ()
On Error resume next
Strname = "F:/book.xls" 'excel file name
Strsheetname = "sheet1" 'excel table name

Conn. open "provider = Microsoft. jet. oledb.4.0; persist Security info = false; Data Source = "& strname &"; extended properties = 'excel 8.0; HDR = Yes '"' connect to an Excel file

SQL = "select * from [" & strsheetname & "$]" 'open an Excel table
Rs. Open SQL, Conn, 3, 3

Msgbox Rs. recordcount
Do until Rs. EOF
For I = 0 to Rs. Fields. Count-1
List1.additem Rs. Fields. Item (I). Name
If not isnull (Rs. Fields. Item (I). Value) then
List2.additem Rs. Fields. Item (I). Value
Else
Rs. Update
Rs. Fields. Item (I). value = "Peter" & I
Rs. Update
End if
Next I
Rs. movenext
Loop
End sub