Excel Cell Ranges using SQL – Named – Unnamed – ODBC – JDBC

How to query Excel files using ODBC - JDBC?

Summary :

  • Why use square brackets for SQL against Excel?
  • How to access range of cells from Excel?
  • How to use un-named range of cells?

This topic is covered in various tutorial for InfoCaptor. These following snippets are taken directly from Microsoft website as easy reference

Select Excel Data with Code
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.

Join InfoCaptor and Get Free Dashboard Software

Posted in Excel, How-to | Tagged , , , , , , , , , ,


Would you like to build your dashboard now?

If so, click to buy now, including 60 minutes of dashboard development for free!