Excel ODBC Driver with ADO

Hi,

I'm using ADO with the Excel ODBC driver to query Excel ‘tables’, identified
in the FROM clause by the worksheet name, i.e. SELECT * FROM [MySheet$].

My ‘table’ (i.e. worksheet) contains 160 rows of data. My problem is that
when querying, the driver believes that 11 empty rows below the data are also
part of the ‘table’.

According to Microsoft, the ‘table’, when querying Excel in this way, is
defined as the ‘UsedRange’ of the worksheet specified - see
http://support.microsoft.com/kb/278973/EN-US/. However, when I check the
number of rows in ActiveWorksheet.UsedRange it correctly consists of the
first 160 rows only.

Although I *could* workaround by building a string referring to the range of
the UsedRange object, and including that in the FROM clause rather than just
the worksheet name, I'd like to work out what's going on!

I'm wondering if it's a bug in the Excel ODBC Driver...

I don't know what is going on either (and hard to test unless I could
duplicate the exact conditions of your code and sheet, including all the
steps used in building the sheet since that often affects the UsedRange).

But one easier and more general workaround would be to add a "WHERE" clause
to your SQL that would reject any blank columns; for example if you have a
column called "NAME" you could specify SELECT * FROM [MySheet$] WHERE NAME
<>''
--
- K Dales


"TimG" wrote:

> Hi,
>
> I'm using ADO with the Excel ODBC driver to query Excel ‘tables’, identified
> in the FROM clause by the worksheet name, i.e. SELECT * FROM [MySheet$].
>
> My ‘table’ (i.e. worksheet) contains 160 rows of data. My problem is that
> when querying, the driver believes that 11 empty rows below the data are also
> part of the ‘table’.
>
> According to Microsoft, the ‘table’, when querying Excel in this way, is
> defined as the ‘UsedRange’ of the worksheet specified - see
> http://support.microsoft.com/kb/278973/EN-US/. However, when I check the
> number of rows in ActiveWorksheet.UsedRange it correctly consists of the
> first 160 rows only.
>
> Although I *could* workaround by building a string referring to the range of
> the UsedRange object, and including that in the FROM clause rather than just
> the worksheet name, I'd like to work out what's going on!
>
> I'm wondering if it's a bug in the Excel ODBC Driver...

The problem could be in the UsedRange, as it might include blank rows but
with formats. You are better off testing for a strong WHERE clause such as

SELECT * FROM [MySheet$] WHERE COL1 <> '' OR COL2 <> ''
OR COL3 <> '' etc


"TimG" wrote:

> Hi,
>
> I'm using ADO with the Excel ODBC driver to query Excel ‘tables’, identified
> in the FROM clause by the worksheet name, i.e. SELECT * FROM [MySheet$].
>
> My ‘table’ (i.e. worksheet) contains 160 rows of data. My problem is that
> when querying, the driver believes that 11 empty rows below the data are also
> part of the ‘table’.
>
> According to Microsoft, the ‘table’, when querying Excel in this way, is
> defined as the ‘UsedRange’ of the worksheet specified - see
> http://support.microsoft.com/kb/278973/EN-US/. However, when I check the
> number of rows in ActiveWorksheet.UsedRange it correctly consists of the
> first 160 rows only.
>
> Although I *could* workaround by building a string referring to the range of
> the UsedRange object, and including that in the FROM clause rather than just
> the worksheet name, I'd like to work out what's going on!
>
> I'm wondering if it's a bug in the Excel ODBC Driver...

The strange thing is that UsedRange returns the correct row count (160 rows)
so the ODBC driver is, apparently, not using UsedRange as documented.

Thank you for your input - using a WHERE clause sounds like a more elegant
workaround than what I proposed!

Tim