Error on MoveNext on Excel recordset

[aatagri]

I am getting an unknown excel error on the last record of an ADO Excel recordset being called from VB6. The code loops through the records, but on the last record the MoveNext method of the recordset causes this unknown error. It almost seems like the recordset is at the end but rs.EOF is false.
The code used to work fine, but then the excel sheets that are being imported were changed. They were previously being generated in Crystal Reports 8, but the version was changed to CR10. Since then I have been getting this error.

Any insight would be appreciated.
Thanks
Andrew

 

[aatagri]

To answer two earlier questions, I don't know how many records will be in the input sheet, so I can't use a counter.

The ADO Error that is reported is the same
"-2147467259: [Microsoft][ODBC Excel Driver] Unknown, Microsoft OLEDB Provider for ODBC Drivers - 22005

 

[aatagri]

I found the problem!! As I said this report is generated from Crystal Reports and exported. The export was causing merged cells in the footer of the report that spanned more than one line. Once I changed the footer to ensure each bit of text went out on one line in Excel, everything worked. Merged on one line were acceptable, but a multi-line merge caused problems both reading and updating the recordset.

Thanks to all who contributed