VBA Access Code EOF does not stop, when records are over

[antonio.manoj.derose]

Hi Friends,

As I promised you guys, am shooting ma first query that I am stuck up with, hope you guys would help me in this.

I have an excel file, and am trying to import it, and once when I have imported it, am checking for a condition with While Not rs.EOF, it works fine for the purpose of looping, but it does not stop when the records are over., it is additionaly printing 19 null statements, where I beleive it, should not be.

Thanks,

Antonio

 

[Catalina]

It would help if you post the entire code.

 

[antonio.manoj.derose]

While Not rs.EOF
' rsIn.AddNew
For i = 0 To rs.Fields.Count - 1
' rsIn.Fields(i + 1) = rs.Fields(i)
Debug.Print rs.Fields(i)

Next i
' rsIn.update


rs.MoveNext
Wend

Above code set, would the above be enough, or do you want me to send from the connection string till the whole lot.

 

[Cronk]

Yes, post the code for the opening of the rs recordset unless the following solves your problem.

However, if rs is based on opening an Excel spreadsheet, it appears that some blank rows are being included.

You need to test for that, and then jump out of the loop. Eg, if data is normally in the A column, test for a blank, or null value to indicate you have moved past the end of the Excel data.

 

[antonio.manoj.derose]

Hi Catalina & Cronk,

Thank you for your speedy inputs, I would check are there any values in the excel.

In the meantime would you be able to help on the same coding, where I just want to pinpoint into the excel sheet mentioning forcefuly to start reading from the 10th row of the excel sheet., the below code makes me print from the very first cell until the end of the records.

While Not rs.EOF
'rsIn.AddNew
For i = 0 To rs.Fields.Count - 1
rsIn.Fields(i + 1) = rs.Fields(i)
Debug.Print rs.Fields(i)
Next i
'rsIn.update
rs.MoveNext
Wend

Thanks,

Antonio

 

[Cronk]

Again, please post the code for the opening of the rs recordset.

:banghead:

 

[Pat Hartman]

PS - when you post code, press the Go Advanced button and use the code tag to keep the formatting so we don't have to indent the code in our heads.

 

[gemma-the-husky]

an excel sheet often includes blank areas when imported.

it seems to have a "history" of the widest and tallest it has ever been - which maybe why you get the blank rows. so your code probably needs to be modified to disregard the blank rows.

 

[Pat Hartman]

Yes, people just highlight cell data and press delete. That clears the values but doesn't "delete" the rows. Excel supports blank rows throughout and people frequently use them to space out the display and make it more readable. These "empty" rows are still there. It's just that when they are surrounded with rows containing data you can "see" them but you can't "see" them when they are at the end of a set of data but they're still there. Empty columns are the same problem when they are to the right of all the populated columns.

To delete a row/column, select the row or column and choose delete row/column from the ribbon. This tells Excel, you really want the row deleted from what Excel thinks of as the set of data for the sheet.

 

[DavidAtWork]

If it is a case of blank rows, try sorting the data on a key column before importing or running a delete query on the import table to remove blank records

David