Hi

i am using this loop to retrieve data from a recordset. The problem is
that when the while loop ends, it goes to the next (line 10) which is
fine, but then it goes to line 3 and not to line 2 where it was
supposed to go to the next c (the next field in the recordset). I have
already tried to put the next just after the for, and it worked fine
(but in this case its only possible to get the fields names or values,
but not all records). i have also tried to replace the while fo another
for... next but it didn´t worked also.

Any clues?

1 With rstRecordset
2 For Each c In .Fields
3 b = b + 1
4 While Not .EOF
5 ThisWorkbook.Sheets("entradas").Select
6 Cells(a, b).Value = c.Value
7 a = a + 1
8 rstRecordset.MoveNext
9 Wend
10 Next c
11 End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I think you have a basic problem with your code. The FOR EACH loop should
be inside the While loop, not the other way around.

With rstRecordSet
Do Until .EOF() = True
A = A + 1
B = 1
For Each C In .Fields
Cells(A, B).Value = C.Value
B = B + 1
Next C
.MoveNext
Loop
End With

You might also want to see the CopyFromRecordSet method. E.g.,

Range("A1").CopyFromRecordSet(rstRecordSet)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"joao" wrote in message
...

Hi

i am using this loop to retrieve data from a recordset. The problem is
that when the while loop ends, it goes to the next (line 10) which is
fine, but then it goes to line 3 and not to line 2 where it was
supposed to go to the next c (the next field in the recordset). I have
already tried to put the next just after the for, and it worked fine
(but in this case its only possible to get the fields names or values,
but not all records). i have also tried to replace the while fo another
for... next but it didn´t worked also.

Any clues?

1 With rstRecordset
2 For Each c In .Fields
3 b = b + 1
4 While Not .EOF
5 ThisWorkbook.Sheets("entradas").Select
6 Cells(a, b).Value = c.Value
7 a = a + 1
8 rstRecordset.MoveNext
9 Wend
10 Next c
11 End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

This must be very slow. Have a look as CopyFromRecordSet
in the help file: line 5 ought to be outside the With
block of code; I can't see the initial value of a.

-----Original Message-----

Hi

i am using this loop to retrieve data from a recordset.
The problem is
that when the while loop ends, it goes to the next (line
10) which is
fine, but then it goes to line 3 and not to line 2 where
it was
supposed to go to the next c (the next field in the
recordset). I have
already tried to put the next just after the for, and it
worked fine
(but in this case its only possible to get the fields
names or values,
but not all records). i have also tried to replace the
while fo another
for... next but it didn´t worked also.

Any clues?

1 With rstRecordset
2 For Each c In .Fields
3 b = b + 1
4 While Not .EOF
5 ThisWorkbook.Sheets("entradas").Select
6 Cells(a, b).Value = c.Value
7 a = a + 1
8 rstRecordset.MoveNext
9 Wend
10 Next c
11 End With


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

but then it goes to line 3 and not to line 2

No it doesn't. That's just an illusion of the debugger.

Let me prove that with a routine very similar to yours but without the ADO
trappings:

Sub Test()
Dim Cell As Range
Dim a As Integer, b As Integer
With Sheet1
For Each Cell In .Range("A1:A10")
b = b + 1
a = 0
While Not a 5
Cell.Offset(0, a).Value = b & " " & a
a = a + 1
Wend
Next Cell
End With
End Sub

While the debugger never (seemingly) gets back to the For Each, you can
clearing see from the output that "Cell" is being incremented.


--
Jim Rech
Excel MVP

It worked fine. Thanks

I wil have a look on the copyfromrecordset!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/