Reg: Incorrect RecordCount when opened a Excel sheet in ADO Recordset

[dev]

Hi All,

I use ADO to open a Excel file using Jet OLEDB Provider.
I have one issue in getting the recordcount.

I open a excel sheet in ADO recordset

I have a Excel that can contain many sheets. I open a excel sheet
using ADO recordset, like

rst.Open ("select * from [Sheet1$]", conn);

Assume , The excel sheet "Sheet1" contain only Column headers and no
data rows.
If I open the excel sheet, the recordset rst shows a recordcount > 0
(it actually showed recordcount as 30) and the EOF property is also
false.

Any idea ?

Thanks in advance
Dev

 

[Tom Ogilvy]

You probably need to go into the sheet and manually delete all the rows
below the header row (entire rows, not cells). then save the workbook. If
you then do Edit=>goto special and select last cell, it will take you to the
last filled cell on the header row (if not, you didn't do it right). Then
you should get the proper count I would think.

 

[keepITcool]

first check the usedrange in the excel sheet.
(ctrl End)
if you have cells containing spaces or formatting
then what appears to be empty is not.


then check:
connect string
clientside cursor


Sub getRecords()
Dim oCon As ADODB.Connection
Dim oRst As ADODB.Recordset

Set oCon = New ADODB.Connection

oCon.CursorLocation = adUseClient
oCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties='Excel 8.0;Header=Yes';" & _
"Data Source=c:\headersonly.xls"

Set oRst = oCon.Execute("Select * from [Sheet1$]")
MsgBox "Should be 0 records:" & vbLf & oRst.RecordCount
oRst.Close
oCon.Close

End Sub

note that since no records were retrieved
both BOF and EOF are true


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


dev wrote :

 

[devi velmurugan]

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

 

[DM Unseen]

I suspect this is not possible in ADO (it is possible in Excel).

A suggestion is:
Loop through all records and check a column for being empty. You could
try to delete them. I'm not sure that will work however, since ADO with
EXCEL has some limitations.

In excel you can do all kind of tricks:

On closing the workbook you could delete all blank rows in the
usedrange

Sub Workbook_close

Thisworkbook.Sheets("Mysheet).UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

end sub

this will delete all rows that are empty in column 1 of your sheet.


DM Unseen