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.
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
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Well, if they all go in order, you can do:
»bp
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial