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

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

Post by dev » Thu, 23 Jun 2005 22:04:25


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
 
 
 

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

Post by keepITcoo » Thu, 23 Jun 2005 22:26:16


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



dev wrote :

 
 
 

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

Post by devi velmu » Fri, 24 Jun 2005 16:12:11


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



*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

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

Post by DM Unsee » Fri, 24 Jun 2005 17:48:38

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