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


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


"Dave Ramage" <(E-Mail Removed)> wrote in message
news:C8F0466C-F0B4-4039-85A7-(E-Mail Removed)...
> 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
>

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

"Douglas J. Steele" wrote:

> 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
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Dave Ramage" <(E-Mail Removed)> wrote in message
> news:C8F0466C-F0B4-4039-85A7-(E-Mail Removed)...
> > 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
> >
>
>
>