Re-post about Loop problem...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry to post this again, but as I had a reply that was
not answering my particular query I felt I should post
again as those posts that receive replies rarely get
another reply.

Hope you guys can help with this!

Many thanks (as always)
Lee

Here's my message:

Hi everyone,
I know I'm being totally dim here but I've just got a
mental block about this. Below is part of some code to
insert the results of a query into an Excel worksheet.
My problem is I want the process to start from the 2nd
row onwards rather than row 1 as there are column
labels
in row 1.
What am I doing wrong here? Here's the code extract:

column = 1

' Loop through the Microsoft Access field names and
create
' the Microsoft Excel labels.
For row = 0 To Rs.Fields.Count - 1
CurrentValue = Rs.Fields(row).Name
Sheet.cells(row + 1, column).Value = CurrentValue
Next row

column = 2

' Loop through the Microsoft Access records and copy
the
records
' to the Microsoft Excel spreadsheet.
Do Until Rs.EOF
For row = 0 To Rs.Fields.Count - 1
CurrentField = Rs(row)
Sheet.cells(row + 1, column).Value =
CurrentField
Next row
Rs.MoveNext
row = row + 1
Loop

Any help would be really appreciated.
Thanks!!
Lee
 
See my response in your previous post

Neil.

Sorry to post this again, but as I had a reply that was
not answering my particular query I felt I should post
again as those posts that receive replies rarely get
another reply.

Hope you guys can help with this!

Many thanks (as always)
Lee

Here's my message:


Hi everyone,
I know I'm being totally dim here but I've just got a
mental block about this. Below is part of some code to
insert the results of a query into an Excel worksheet.
My problem is I want the process to start from the 2nd
row onwards rather than row 1 as there are column
labels
in row 1.
What am I doing wrong here? Here's the code extract:

column = 1

' Loop through the Microsoft Access field names and
create
' the Microsoft Excel labels.
For row = 0 To Rs.Fields.Count - 1
CurrentValue = Rs.Fields(row).Name
Sheet.cells(row + 1, column).Value = CurrentValue
Next row

column = 2

' Loop through the Microsoft Access records and copy
the
records
' to the Microsoft Excel spreadsheet.
Do Until Rs.EOF
For row = 0 To Rs.Fields.Count - 1
CurrentField = Rs(row)
Sheet.cells(row + 1, column).Value =
CurrentField
Next row
Rs.MoveNext
row = row + 1
Loop

Any help would be really appreciated.
Thanks!!
Lee
Click to expand...
 
Hi Lee,

You are using a counter named row as the index for the fields collection -
this is a bit confusing. Look at the second part of your code:

Do Until Rs.EOF
For row = 0 To Rs.Fields.Count - 1
CurrentField = Rs(row)
Sheet.cells(row + 1, column).Value = CurrentField
Next row
Rs.MoveNext
row = row + 1
Loop

.. . . the variable named row is getting incremented in the inner loop and
then incremented once in the outside loop. Then set back to 0 each time the
inner loop is initialized. Thus in this line:

Sheet.cells(row + 1, column).Value = CurrentField

The value Row + 1 is actually the fieldnumber + 1. And since column is
hardcoded to 2, every value will be put in column 2.

Here is some code that is structured a bit differently. Notice that I'm
using two counters, curCol and curRow. CurRow is incremented in the outside
loop and curCol is incremented in the inside loop.

'Assign Column Labels
curCol = 0
For Each fld In rst.Fields
curCol = curCol + 1
sheet.Cells(1, curCol) = fld.Name
Next fld

'Copy Data
curCol = 0
curRow = 1
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
curRow = curRow + 1
For Each fld In rst.Fields
curCol = curCol + 1
sheet.Cells(curRow, curCol) = fld.Value
Next fld
curCol = 0
.MoveNext
Loop
End If
.Close
End With
 
Back
Top