How to use ADO to read and write data in Excel workbooks using ExcelADO?

[Leonard]

Hello everyone,
I am having knowledge about the ADO (ActiveX Data Objects) and the MS Office to better extent. Now I want to use ADO with the Microsoft Jet OLE DB 4.0, so that it can read and write data in Microsoft Excel workbooks. So please tell me how to use ADO to read and write data in Excel workbooks using ExcelADO? Hoping that someone would reply me soon.!!

[Raine]

Using ADO to transfer data to an Excel workbook or retrieve data from an Excel workbook presents to you the developer several advantages over the Excel Automation :

• Performance. Microsoft Excel is an ActiveX server out-of-process. ADO runs in-process and avoids costly call out-of-process.
• Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO is a more scalable solution for managing data in a workbook.

[Kirmiac]

The Database Engine Microsoft Jet database can be used to access data in other formats file database such as Excel workbooks, through pilot ISAM (Indexed Sequential Access Method) installable. To open external formats supported by the OLE DB Provider for Microsoft Jet 4.0, you specify the type of database in the extended properties for the connection. The OLE DB Provider for Microsoft Jet supports standard database for the following Microsoft Excel workbooks :

• Excel 3.0
• Excel 4.0
• Excel 5.0
• Excel 8.0

[DANIEL 602]

The ADO can be used solely for transferring raw data to a workbook. You can not use ADO to apply formats and formulas to cells. However, you can transfer data to a workbook previously formatted and the format is retained. If you need a formatted "conditional" after inserting data, you can do so using automation or a macro in the workbook. With Excel workbooks, the first line of a beach is considered as row header (or field names) by default. If the first range does not contain header, you can specify HDR = NO in the extended properties of your connection string. If the first line does not contain headers, the OLE DB automatically names the fields for you.

[NIcaBoy]

Unlike a traditional database, there is no direct way to specify data types for columns in Excel tables. Instead, the OLE DB analyzes a limited number of rows in a column to "guess" the datatype for the field. The number of rows to scan defaults to the value of eight (8) lines, you can change this number by specifying a value between one (1) and sixteen (16) for setting MaxScanRows in the extended properties of your connection string. ADO model implements the OLE DB provider that allows it through "PROVIDER" to access all data sources. OLE DB does not replace ODBC, but it provides a "PROVIDER" which allows access to ODBC data sources.

[Rob Dizzle]

If you are using the Visual Basic, then follow the steps mentioned below :

1. In Visual Basic, open the file ExcelADO.vbp.
2. In the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library.
3. Press F5 to run the program. A form appears for the demonstration.
4. Click on Example 1. This example creates a copy of the file OrdersTemplate.xls. It then uses ADO to connect to the workbook and open a recordset on a table which is a defined range in the workbook.
5. Click on Example 2. This example creates a copy of the file EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) in the workbook.
6. Click on Example 3. This example creates a copy of the file ProductsTemplate.xls. It uses Object Library Microsoft ADO Extensions for DDL and Security (ADOX) version 2.1 to add a new table (or a new worksheet) to the workbook.
7. The last example illustrates how to read data from an Excel workbook. Select a table in the dropdown menu, then click on Example 4.