method to fills cells on sheet without using recordset

Fordraiders
Fordraiders used Ask the Experts™
on
vba excel 2010

dbconnectstring is from sql server.

What I'am doing:

Calling a sql server and putting data from that query in the sheet.

Currently using a recordset:


db.CursorLocation = adUseClient
   db.Open dbconnstring
   db.CommandTimeout = 6000
                With rs
                 .ActiveConnection = db
                 .Open sQuery
                End With
       cArrSql = rs.GetRows
        rs.MoveFirst
       
 Range("B6").Select

Do While rs.EOF = False
' ActiveCell.EntireRow.Insert ' insert a row to preserve original values
On Error Resume Next
 ActiveCell.Offset(0, 0).Value = rs.Fields(0)
    ActiveCell.Offset(0, 1).Value = rs.Fields(1)
     ActiveCell.Offset(0, 2).Value = rs.Fields(2)

etc.....

 rs.MoveNext
Loop


Is there  a faster way to present the data on the sheet?


thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi, you can use Range.CopyFromRecordset (http://msdn.microsoft.com/en-us/library/office/aa223845(v=office.11).aspx)

Range("B6").CopyFromRecordSet rs

Open in new window

Author

Commented:
Thanks. Worked great

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