asked on

VBA dao recordset: easy way to run query on db and paste results into excel spreadsheet

If i query an access db from excel in vba how can i output them into a worksheet easily without looping throuh?????

Set db = DBEngine.OpenDatabase(idDatabasePath2)
Set qdf = db.QueryDefs("DM TRADE INFO")
With qdf
.Parameters(0) = InputBox("name me a feed")
End With
Set rst = qdf.OpenRecordset

Arthur_Wood

once you have the recordset, you will need to explicitly iterate record by record, and field by field within each record, to assign the values to the cells in your worksheet. There is no other way.

Dim iField as Integer

do while not rst.EOF
For iField = 0 to rst.Fields.Count -1
ActiveCell.Value = rst.Fields(iField).Value
Next iField
ActiveCell.Offset(0,1)
rst.MoveNext
Loop

AW

AW

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mvidas

I should say that there are other methods, like returning the recordset to an array then entering into the sheet:

Dim RetVals() As Variant
RetVals = rst.GetRows
Range("A2").Resize(UBound(RetVals, 1) + 1, UBound(RetVals, 2) + 1).Value = RetVals

Though using the copyfromrecordset method is usually the easiest

david_88

ASKER

works a charm

thanks 4 the support guys

mvidas

One nice thing about copyfromrecordset is that it will only copy over the first 65536 rows (the excel limit). You could probably test somehow (I'm not too familiar with DAO) to see if its at the end of the recordset, and if not then create another sheet and repeat the copyfromrecordset on that
Glad to help