Select Excel Data with Code using ADO Object

Your Excel data may be contained in your workbook in one of the following:

• An entire worksheet.
• A named range of cells on a worksheet.
• An unnamed range of cells on a worksheet.

Specify a Worksheet

To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

 strQuery = "SELECT * FROM [Sheet1$]"
    

You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example:

 strQuery = "SELECT * FROM `Sheet1$`"
    

Microsoft prefers the square brackets, which are the standing convention for problematic database object names.

If you omit both the dollar sign and the brackets, or just the dollar sign, you receive the following error message:

... the Jet database engine could not find the specified object

If you use the dollar sign but omit the brackets, you will see the following error message:

Syntax error in FROM clause.

If you try to use ordinary single quotes, you receive the following error message:

Syntax error in query. Incomplete query clause. 

 

Specify a Named Range

To specify a named range of cells as your recordsource, simply use the defined name. For example:

 strQuery = "SELECT * FROM MyRange"
    

Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

 strQuery = "SELECT * FROM [Sheet1$A1:B10]"
    

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.

With MDAC versions prior to 2.5, when you specify a named range, you cannot add new records beyond the defined limits of the range, or you receive the following error message:

Cannot expand named range.

Select Excel Data with the ADO Data Control

After you specify the connection settings for your Excel data source on the General tab of the ADODC Properties dialog box, click on the Recordsource tab. If you choose a CommandType of adCmdText, you can enter a SELECT query in the Command Text dialog box with the syntax described previously. If you choose a CommandType of adCmdTable, and you are using the Jet Provider, the drop-down list displays both the named ranges and worksheet names that are available in the selected workbook, with named ranges listed first.

This dialog box properly appends the dollar sign to worksheet names, but does not add the necessary square brackets. As a result, if you simply select a worksheet name and click OK, you receive the following error message later:

Syntax error in FROM clause.

You must manually add the square brackets around the worksheet name. (This combo box does allow editing.) If you are using the ODBC Provider, you see only named ranges listed in this drop-down list. However, you can manually enter a worksheet name with the appropriate delimiters.

Select Excel Data with Data Environment Commands

After setting up the Data Environment Connection for your Excel data source, create a new Command object. If you choose a Source of Data of SQL Statement, you can enter a query in the textbox using the syntax described previously. If you choose a Source of Data of Database Object, select Table in the first drop-down list, and you are using the Jet Provider, the drop-down list displays both named ranges and worksheet names available in the selected workbook, with named ranges listed first. (If you choose a worksheet name in this location, you do not need to add square brackets around the worksheet name manually as you do for the ADO Data Control.) If you are using the ODBC Provider, you see only named ranges listed in this drop-down list. However, you can manually enter a worksheet name.

How to Change Excel Data: Edit, Add, and Delete

Edit

You can edit Excel data with the normal ADO methods. Recordset fields which correspond to cells in the Excel worksheet containing Excel formulas (beginning with "=") are read-only and cannot be edited. Remember that an ODBC connection to Excel is read-only by default, unless you specify otherwise in your connection settings. See earlier under "Using the Microsoft OLE DB Provider for ODBC Drivers."

Add

You can add records to your Excel recordsource as space allows. However, if you add new records outside the range that you originally specified, these records are not visible if you requery on the original range specification. See earlier under "A caution about specifying ranges."

In certain circumstances, when you use the AddNew and Update methods of the ADO Recordset object to insert new rows of data into an Excel table, ADO may insert the data values into the wrong columns in Excel. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

314763  FIX: ADO Inserts Data into Wrong Columns in Excel

Delete

You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

1. Delete an entire record at once or you receive the following error message:
   Deleting data in a linked table is not supported by this ISAM.
   You can only delete a record by blanking out the contents of each individual field.
2. Delete the value in a cell containing an Excel formula or you receive the following error message:
   Operation is not allowed in this context.
3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located, and your recordset will continue to display empty records corresponding to these empty rows.

A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.

Source : support.microsoft.com