Copy query, paste into Excel as data with VBA..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi...

If I copy a query manually (by selecting it in the Database Window, then
Ctrl + C), I can then paste the results into Excel by simply pasting. Is
there a way of doing this from VBA- e.g.

Sub Test
Dim xlApp as Excel.Application
Dim wbTarget as Excel.Workbook

Set xlApp = New Excel.Application
Set wbTarget = xlApp.Workbooks.Add

CurrentDB.QueryDefs("qryTest").Copy 'no such method!
wbTarget.Sheets(1).Range("A1").Paste
End Sub

I could export the query as an Excel file, open it, then copy the worksheet
across...but a simple copy and paste would be easier if possible.

Thanks,
Dave
 
I believe the following should work:

Sub Test
Dim xlApp as Excel.Application
Dim wbTarget as Excel.Workbook
Dim rsCurr As DAO.Recordset

Set xlApp = New Excel.Application
Set wbTarget = xlApp.Workbooks.Add

Set rsCurr = CurrentDB.QueryDefs("qryTest").OpenRecordset
wbTarget.Sheets(1).Range("A1").CopyFromRecordset rsCurr

End Sub
 
Thanks Douglas- works fine- even accepts an ADO recordset, which I happened
to have handy...
 
Back
Top