Problem with loops!

  • Thread starter Thread starter Lee
  • Start date Start date
L

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
 
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.
 
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
 
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.
 
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
 
Back
Top