simpler way to write this code for reading records from Access and posting to excel worksheet

Excel vba
Access

I have a bit of code that is reading a recordset from Access.

While Not Rec.EOF

  cell.Offset(0, 30).Value = Rec.Fields(1)
  cell.Offset(0, 31).Value = Rec.Fields(2)
  cell.Offset(0, 32).Value = Rec.Fields(3)
  cell.Offset(0, 33).Value = Rec.Fields(4)
  cell.Offset(0, 34).Value = Rec.Fields(5)
  cell.Offset(0, 35).Value = Rec.Fields(6)
  cell.Offset(0, 36).Value = Rec.Fields(7)
  cell.Offset(0, 37).Value = Rec.Fields(8)
  cell.Offset(0, 38).Value = Rec.Fields(9)
  cell.Offset(0, 39).Value = Rec.Fields(10)
  cell.Offset(0, 40).Value = Rec.Fields(11)
  cell.Offset(0, 41).Value = Rec.Fields(12)
  cell.Offset(0, 42).Value = Rec.Fields(13)
  cell.Offset(0, 43).Value = Rec.Fields(14)
  cell.Offset(0, 44).Value = Rec.Fields(15)

Rec.MoveNext
Wend

Rec.Close

Open in new window


I'm starting (to post) at a certain cell on the worksheet and a field(from access)

I'm gonna have 55 fields to fill the cells in Excel.

is there a simpler way to write this code for reading records from Access and posting to excel worksheet?

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Well, if they all go in order, you can do:

While Not Rec.EOF
    For i = 1 to 15
        cell.Offset(0, 29+i).Value = Rec.Fields(i)
    Next
    Rec.MoveNext
Wend

Rec.Close

Open in new window

»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Bill, Thanks seems so simple!
0
PatHartmanCommented:
If the target area is table shaped, you can use TransferSpreadsheet with a named range argument.  As long as what you are posting always fits within the named range, there won't be a problem  If the data sent to the named range is to large to fit, there will be an error.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
How about just this?

Range("YourStartingCellHere").CopyFromRecordset Rec

Open in new window


e.g. if your starting cell is A1 then...
Range("A1").CopyFromRecordset Rec

Open in new window

0
FordraidersAuthor Commented:
thanks very much !!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.