Problem with loops!

[Lee]

He 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

 

[Frank Stone]

if you are simply trying to download a query, why not
write a access macro and use the transfer spredsheet
action. i use it all the time. works great.

 

[Lee]

Hi,
No, I'm not trying to just transfer a query to Excel.
The destination spreadsheet contains all sorts of
(percentage) analysis of the figures, formatting and
charts etc...
Can you help with the Loop problem?
Thanks
Lee

 

[Neil]

Lee,

Start the row count from 1.

For row = 1 To Rs.Fields.Count - 1
CurrentValue = Rs.Fields(row).Name
Sheet.cells(row + 1, column).Value = CurrentValue
Next row

HTH,

Neil.

 

[Guest]

You are trying to oversimplify, separate out the variables instead of using
one. Try the following:

..
..
..
column = 1
row = 2
For FieldPos = 0 To Rs.Fields.Count-1
CurrentValue = Rs.Fields(FieldPos).Name
Sheet.cells(row, column).Value = CurrentValue
row = row + 1
Next FieldPos

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

good luck,

Brian