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

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
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

Author

Commented:
Bill, Thanks seems so simple!
Distinguished Expert 2017
Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

Commented:
thanks very much !!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial