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

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.

LVL 63

Bill PrewIT / Software Engineering ConsultantCommented: 2017-10-27

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

»bp

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

LVL 3

FordraidersAuthor Commented: 2017-10-27

Bill, Thanks seems so simple!

LVL 44

PatHartmanCommented: 2017-10-27

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.

LVL 43

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2017-10-27

How about just this?

Range("YourStartingCellHere").CopyFromRecordset Rec

e.g. if your starting cell is A1 then...

Range("A1").CopyFromRecordset Rec

FordraidersAuthor Commented: 2017-10-30

thanks very much !!!

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

Who is Participating?