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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Ramage - 01 Aug 2007 17:36 GMT

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

Reply to this Message

Douglas J. Steele - 01 Aug 2007 18:00 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi...
>
[quoted text clipped - 19 lines]
> Thanks,
> Dave

Reply to this Message

Dave Ramage - 01 Aug 2007 23:52 GMT

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

> I believe the following should work:
>
[quoted text clipped - 34 lines]
> > Thanks,
> > Dave

Reply to this Message