Reg: ADO recordset returns wrong recordcount of a Excel worksheet

[dev]

Hi All,

I am using ADO to read Excel sheet using Jet OLEDB Providers

The issue is,


I open a Excel worksheet like,

strSql = "select * from [Sheet1$]"

Assume the Sheet1 constains no data other than the column headers
My ADo recordset which opens this sheet returns some junk recordcount
(it returned the recordcount as 30) and the EOF is also false.

I would like to know how to check an empty worksheet.
I use Static client side curosr

Please help...

thanks in advance
dev

[Bill Manville]

Are you sure the sheet is empty?
If you open it in Excel and use Ctrl+End where does the cursor go?
Are there any defined names (Insert / Names / Define) on the sheet?

Bill Manville
MVP - Microsoft Excel, Oxford, England

[dev]

Hi,

Thanks.

The control went to the 30th row. Now I deleted the row correctly and
the problem is solved (I have deleted the data in cells and not the
actual row).

But I have another question..
This excel sheet will be given by the end user and my system loads it
and scans for the record..no manual opening in between this process. If
the user had made the same mistake that I did...( not deleting the
rows) then is there any way to handle the problem.?

Also,
there might be some possibilities that the excel sheet to contain blank
rows inbetween the data records. I need to find that and remove all
those..

I am using ASP to open the excel file and reading it.

I would like to do all this using ADO?

Thanks

devi

[p***@jetemail.net]

Post by dev
This excel sheet will be given by the end user and my system loads it
and scans for the record..no manual opening in between this process. If
the user had made the same mistake that I did...( not deleting the
rows) then is there any way to handle the problem.?
Also,
there might be some possibilities that the excel sheet to contain blank
rows inbetween the data records. I need to find that and remove all
those..
I am using ASP to open the excel file and reading it.
I would like to do all this using ADO?

A table is not a table unless it has a key:

select * from [Sheet1$] WHERE key_column IS NOT NULL

select * from [Sheet1$] WHERE key_column <> 0

select * from [Sheet1$] WHERE LEN(key_column) > 0

depeding on the data type and what you mean by "blank".

[Bill Manville]

I think you are onto a loser trying to use ADO to remove rows that ADO
does not think form part of the recordset.

However, as I haven't used ADO to access Excel worksheets I can't speak
from experience; I will go away and hope someone else can answer.

Bill Manville
MVP - Microsoft Excel, Oxford, England